# Video Courses Feature Implementation Plan

## Overview
Add a comprehensive Learning Management System (LMS) to Jadwaai for video courses with trainer management, bulk video uploads, and certificate generation.

## Tech Stack
- **Framework**: Flask 3.1.0
- **Database**: PostgreSQL with SQLAlchemy ORM
- **Frontend**: Tailwind CSS + Alpine.js
- **Storage**: Local filesystem (`/app/static/uploads/courses/`)
- **Existing patterns**: Follow admin blueprint pattern, use existing auth

---

## 1. Database Models

### 1.1 Course Category
```python
# app/models/course.py

class CourseCategory(db.Model):
    __tablename__ = "course_categories"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    name_ar = db.Column(db.String(255), nullable=False)
    slug = db.Column(db.String(255), unique=True, nullable=False)
    description = db.Column(db.Text, nullable=True)
    description_ar = db.Column(db.Text, nullable=True)
    icon = db.Column(db.String(50), nullable=True)
    color = db.Column(db.String(20), default="#D2AF20")
    display_order = db.Column(db.Integer, default=0)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Relationships
    courses = db.relationship("Course", backref="category", lazy="dynamic")
```

### 1.2 Trainer
```python
class Trainer(db.Model):
    __tablename__ = "trainers"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    name_ar = db.Column(db.String(255), nullable=True)
    bio = db.Column(db.Text, nullable=True)
    bio_ar = db.Column(db.Text, nullable=True)
    title = db.Column(db.String(255), nullable=True)  # Job title
    title_ar = db.Column(db.String(255), nullable=True)
    avatar_url = db.Column(db.String(500), nullable=True)
    email = db.Column(db.String(255), nullable=True)
    linkedin_url = db.Column(db.String(500), nullable=True)
    twitter_url = db.Column(db.String(500), nullable=True)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Relationships
    courses = db.relationship("Course", backref="trainer", lazy="dynamic")
```

### 1.3 Course
```python
class Course(db.Model):
    __tablename__ = "courses"

    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey("course_categories.id"), nullable=False)
    trainer_id = db.Column(db.Integer, db.ForeignKey("trainers.id"), nullable=False)

    # Basic Info
    title = db.Column(db.String(500), nullable=False)
    title_ar = db.Column(db.String(500), nullable=False)
    slug = db.Column(db.String(255), unique=True, nullable=False)
    subtitle = db.Column(db.String(500), nullable=True)
    subtitle_ar = db.Column(db.String(500), nullable=True)

    # Description
    description = db.Column(db.Text, nullable=False)
    description_ar = db.Column(db.Text, nullable=False)

    # Media
    cover_image = db.Column(db.String(500), nullable=True)
    promo_video_url = db.Column(db.String(500), nullable=True)

    # Course Details
    level = db.Column(db.String(50), default="beginner")  # beginner, intermediate, advanced
    language = db.Column(db.String(10), default="ar")  # ar, en, both
    duration_hours = db.Column(db.Float, default=0)

    # Pricing
    price = db.Column(db.Float, default=0)
    currency = db.Column(db.String(10), default="SAR")
    is_free = db.Column(db.Boolean, default=False)

    # Enrollment & Limits
    max_students = db.Column(db.Integer, nullable=True)  # null = unlimited
    current_enrollments = db.Column(db.Integer, default=0)

    # Requirements
    requirements_json = db.Column(db.JSON, default=list)  # List of requirements
    requirements_ar_json = db.Column(db.JSON, default=list)

    # What you'll learn
    learning_objectives_json = db.Column(db.JSON, default=list)
    learning_objectives_ar_json = db.Column(db.JSON, default=list)

    # Target audience
    target_audience_json = db.Column(db.JSON, default=list)
    target_audience_ar_json = db.Column(db.JSON, default=list)

    # Certificate
    certificate_template_id = db.Column(db.Integer, db.ForeignKey("certificate_templates.id"), nullable=True)
    certificate_threshold = db.Column(db.Float, default=80.0)  # % completion to get certificate

    # Status
    status = db.Column(db.String(20), default="draft")  # draft, published, archived
    featured = db.Column(db.Boolean, default=False)
    is_active = db.Column(db.Boolean, default=True)

    # SEO
    meta_title = db.Column(db.String(255), nullable=True)
    meta_description = db.Column(db.Text, nullable=True)

    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    published_at = db.Column(db.DateTime, nullable=True)

    # Relationships
    videos = db.relationship("CourseVideo", backref="course", lazy="dynamic", order_by="CourseVideo.position")
    enrollments = db.relationship("CourseEnrollment", backref="course", lazy="dynamic")
    reviews = db.relationship("CourseReview", backref="course", lazy="dynamic")
```

### 1.4 Course Video
```python
class CourseVideo(db.Model):
    __tablename__ = "course_videos"

    id = db.Column(db.Integer, primary_key=True)
    course_id = db.Column(db.Integer, db.ForeignKey("courses.id"), nullable=False)

    # Video Info
    title = db.Column(db.String(500), nullable=False)
    title_ar = db.Column(db.String(500), nullable=False)
    description = db.Column(db.Text, nullable=True)
    description_ar = db.Column(db.Text, nullable=True)

    # Video File
    video_path = db.Column(db.String(500), nullable=False)  # Path to uploaded video
    video_url = db.Column(db.String(500), nullable=True)  # External URL (YouTube, Vimeo)
    duration_seconds = db.Column(db.Integer, default=0)
    thumbnail_path = db.Column(db.String(500), nullable=True)

    # Organization
    section_name = db.Column(db.String(255), nullable=True)  # For grouping videos
    section_name_ar = db.Column(db.String(255), nullable=True)
    position = db.Column(db.Integer, default=0)

    # Access
    is_preview = db.Column(db.Boolean, default=False)  # Free preview video
    is_active = db.Column(db.Boolean, default=True)

    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
```

### 1.5 Course Enrollment
```python
class CourseEnrollment(db.Model):
    __tablename__ = "course_enrollments"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey("courses.id"), nullable=False)

    # Progress
    progress_percentage = db.Column(db.Float, default=0)
    completed_videos_json = db.Column(db.JSON, default=list)  # List of video IDs
    last_watched_video_id = db.Column(db.Integer, db.ForeignKey("course_videos.id"), nullable=True)
    last_position_seconds = db.Column(db.Integer, default=0)  # Resume position

    # Status
    status = db.Column(db.String(20), default="enrolled")  # enrolled, in_progress, completed, dropped
    enrolled_at = db.Column(db.DateTime, default=datetime.utcnow)
    completed_at = db.Column(db.DateTime, nullable=True)
    certificate_issued = db.Column(db.Boolean, default=False)
    certificate_id = db.Column(db.Integer, db.ForeignKey("certificates.id"), nullable=True)

    # Unique constraint
    __table_args__ = (db.UniqueConstraint("user_id", "course_id", name="uq_user_course"),)

    user = db.relationship("User", backref="enrollments")
    last_watched_video = db.relationship("CourseVideo", foreign_keys=[last_watched_video_id])
```

### 1.6 Certificate Template
```python
class CertificateTemplate(db.Model):
    __tablename__ = "certificate_templates"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    name_ar = db.Column(db.String(255), nullable=False)

    # Template Design
    background_image = db.Column(db.String(500), nullable=False)
    logo_position = db.Column(db.String(20), default="top")  # top, top-left, top-right
    text_color = db.Column(db.String(20), default="#1a1a1a")
    accent_color = db.Column(db.String(20), default="#D2AF20")
    font_family = db.Column(db.String(100), default="Amiri")

    # Certificate Text
    title_text = db.Column(db.String(255), default="شهادة إتمام")
    title_text_ar = db.Column(db.String(255), default="شهادة إتمام")
    body_template = db.Column(db.Text, default="تشهد أكاديمية جدوى بأن الطالب [name] أتم بنجاح الدورة التدريبية [course]")
    body_template_ar = db.Column(db.Text, nullable=True)

    # Signature
    signature_text = db.Column(db.String(255), nullable=True)
    signature_image = db.Column(db.String(500), nullable=True)

    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    courses = db.relationship("Course", backref="certificate_template")
```

### 1.7 Certificate (Issued)
```python
class Certificate(db.Model):
    __tablename__ = "certificates"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey("courses.id"), nullable=False)
    template_id = db.Column(db.Integer, db.ForeignKey("certificate_templates.id"), nullable=False)

    # Certificate Details
    certificate_number = db.Column(db.String(100), unique=True, nullable=False, index=True)
    issued_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Verification
    verification_url = db.Column(db.String(500), nullable=True)

    user = db.relationship("User", backref="certificates")
    course = db.relationship("Course", backref="certificates")
    template = db.relationship("CertificateTemplate")
```

### 1.8 Course Review
```python
class CourseReview(db.Model):
    __tablename__ = "course_reviews"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey("courses.id"), nullable=False)

    rating = db.Column(db.Integer, nullable=False)  # 1-5
    comment = db.Column(db.Text, nullable=True)
    is_approved = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship("User", backref="course_reviews")

    __table_args__ = (db.UniqueConstraint("user_id", "course_id", name="uq_user_course_review"),)
```

---

## 2. File Structure

```
app/
├── models/
│   └── course.py              # All course-related models
├── routes/
│   ├── admin.py               # Add course admin routes
│   ├── courses.py             # New: User-facing course routes
│   └── api_courses.py         # New: API endpoints for video player
├── templates/
│   ├── admin/
│   │   ├── courses.html               # Course list
│   │   ├── course_form.html           # Add/edit course
│   │   ├── course_videos.html         # Manage course videos
│   │   ├── bulk_video_upload.html     # Bulk upload interface
│   │   ├── course_categories.html     # Category management
│   │   ├── trainers.html              # Trainer management
│   │   ├── certificates.html          # Certificate templates
│   │   └── certificate_templates.html # Template management
│   └── courses/
│       ├── index.html                 # Course catalog
│       ├── course_detail.html         # Single course page
│       ├── course_player.html         # Video learning interface
│       ├── my_courses.html            # User's enrolled courses
│       └── certificate_view.html      # View issued certificate
├── static/
│   └── uploads/
│       └── courses/
│           ├── videos/
│           │   └── <course_id>/
│           ├── thumbnails/
│           ├── covers/
│           └── certificates/
└── helpers/
    └── certificate_generator.py  # Certificate PDF generation
```

---

## 3. Admin Routes (`app/routes/admin.py`)

### 3.1 Categories Management
- `GET /admin/course-categories` - List categories
- `POST /admin/course-categories/save` - Create/update category
- `POST /admin/course-categories/<id>/toggle` - Toggle active
- `POST /admin/course-categories/<id>/delete` - Delete category

### 3.2 Trainers Management
- `GET /admin/trainers` - List trainers
- `POST /admin/trainers/save` - Create/update trainer
- `POST /admin/trainers/<id>/toggle` - Toggle active
- `POST /admin/trainers/<id>/delete` - Delete trainer

### 3.3 Courses Management
- `GET /admin/courses` - List all courses with filters
- `GET /admin/courses/new` - New course form
- `GET /admin/courses/<id>/edit` - Edit course
- `POST /admin/courses/save` - Create/update course
- `POST /admin/courses/<id>/toggle` - Toggle published
- `POST /admin/courses/<id>/feature` - Toggle featured
- `POST /admin/courses/<id>/delete` - Delete course

### 3.4 Course Videos Management
- `GET /admin/courses/<id>/videos` - Manage course videos
- `POST /admin/courses/<id>/videos/upload` - Upload single video
- `POST /admin/courses/<id>/videos/bulk-upload` - Bulk upload videos
- `POST /admin/courses/<course_id>/videos/<video_id>/update` - Update video details
- `POST /admin/courses/<course_id>/videos/<video_id>/delete` - Delete video
- `POST /admin/courses/<course_id>/videos/reorder` - Reorder videos

### 3.5 Certificate Templates
- `GET /admin/certificate-templates` - List templates
- `POST /admin/certificate-templates/save` - Create/update template
- `POST /admin/certificate-templates/<id>/delete` - Delete template

### 3.6 Enrollments & Analytics
- `GET /admin/course-enrollments` - View all enrollments
- `GET /admin/courses/<id>/students` - Course students list
- `GET /admin/courses/<id>/analytics` - Course analytics

---

## 4. User-Facing Routes (`app/routes/courses.py`)

### 4.1 Course Catalog
- `GET /courses` - Course catalog with filters
- `GET /courses/category/<slug>` - Courses by category
- `GET /courses/<slug>` - Course detail page

### 4.2 Enrollment
- `POST /courses/<id>/enroll` - Enroll in course (free or paid)
- `GET /my-courses` - User's enrolled courses

### 4.3 Learning Interface
- `GET /learn/<course_id>` - Course player/learning interface
- `GET /learn/<course_id>/<video_id>` - Play specific video
- `POST /api/courses/<course_id>/progress` - Update progress (AJAX)
- `POST /api/courses/<course_id>/video/<video_id>/complete` - Mark video complete
- `POST /api/courses/<course_id>/save-position` - Save playback position

### 4.4 Reviews
- `POST /courses/<id>/review` - Submit review
- `GET /courses/<id>/reviews` - View course reviews

### 4.5 Certificates
- `GET /certificates` - User's certificates
- `GET /certificates/<id>` - View certificate
- `GET /certificates/<id>/download` - Download PDF
- `GET /verify/<certificate_number>` - Public verification page

---

## 5. Bulk Video Upload Implementation

### 5.1 Upload Flow
1. Admin selects course
2. Uploads multiple video files (MP4, WebM)
3. System processes videos:
   - Extract thumbnails
   - Get duration
   - Store in organized directory structure
4. Admin enters titles/descriptions for each video
5. Videos are added to course

### 5.2 Backend Processing
```python
# app/helpers/video_processor.py

import os
import uuid
from werkzeug.utils import secure_filename
from moviepy.editor import VideoFileClip

ALLOWED_VIDEO_EXTENSIONS = {'mp4', 'webm', 'mov', 'avi'}
MAX_VIDEO_SIZE = 500 * 1024 * 1024  # 500MB per video

def process_uploaded_video(file, course_id):
    """Process uploaded video: save, extract thumbnail, get duration"""
    # Generate unique filename
    filename = secure_filename(file.filename)
    unique_name = f"{uuid.uuid4().hex}_{filename}"

    # Save video
    video_path = f"uploads/courses/videos/{course_id}/{unique_name}"
    full_path = f"app/static/{video_path}"
    os.makedirs(os.path.dirname(full_path), exist_ok=True)
    file.save(full_path)

    # Extract thumbnail
    thumbnail_path = extract_thumbnail(full_path, course_id, unique_name)

    # Get duration
    duration = get_video_duration(full_path)

    return {
        "video_path": video_path,
        "thumbnail_path": thumbnail_path,
        "duration_seconds": duration
    }

def extract_thumbnail(video_path, course_id, filename):
    """Extract thumbnail from video"""
    clip = VideoFileClip(video_path)
    thumbnail_path = f"uploads/courses/thumbnails/{course_id}/{filename}.jpg"
    full_thumbnail_path = f"app/static/{thumbnail_path}"
    os.makedirs(os.path.dirname(full_thumbnail_path), exist_ok=True)
    clip.save_frame(full_thumbnail_path, t=1)  # Frame at 1 second
    clip.close()
    return thumbnail_path

def get_video_duration(video_path):
    """Get video duration in seconds"""
    clip = VideoFileClip(video_path)
    duration = int(clip.duration)
    clip.close()
    return duration
```

---

## 6. Certificate Generation

### 6.1 PDF Generation (using ReportLab or WeasyPrint)
```python
# app/helpers/certificate_generator.py

from datetime import datetime
import os
import uuid
from weasyprint import HTML

def generate_certificate_pdf(enrollment):
    """Generate certificate PDF for completed course"""
    user = enrollment.user
    course = enrollment.course
    template = enrollment.certificate_template or course.certificate_template

    certificate_number = f"JC-{datetime.utcnow().strftime('%Y%m%d')}-{uuid.uuid4().hex[:8].upper()}"

    # Create certificate record
    cert = Certificate(
        user_id=user.id,
        course_id=course.id,
        template_id=template.id if template else 1,
        certificate_number=certificate_number
    )
    db.session.add(cert)
    db.session.commit()

    # Generate PDF
    html_template = render_template('courses/certificate_pdf.html',
        user=user,
        course=course,
        template=template,
        certificate_number=certificate_number,
        date=datetime.utcnow().strftime('%Y-%m-%d')
    )

    pdf_path = f"uploads/courses/certificates/{certificate_number}.pdf"
    full_path = f"app/static/{pdf_path}"
    os.makedirs(os.path.dirname(full_path), exist_ok=True)

    HTML(string=html_template).write_pdf(full_path)

    # Update enrollment
    enrollment.certificate_id = cert.id
    enrollment.certificate_issued = True
    db.session.commit()

    return pdf_path
```

---

## 7. API Endpoints for Video Player

### 7.1 Progress Tracking
```python
# app/routes/api_courses.py

@api_bp.route("/courses/<int:course_id>/progress", methods=["POST"])
@login_required
def update_progress(course_id):
    """Update user's progress in course"""
    enrollment = CourseEnrollment.query.filter_by(
        user_id=current_user.id,
        course_id=course_id
    ).first_or_404()

    data = request.get_json()
    video_id = data.get("video_id")
    position = data.get("position", 0)

    if video_id:
        enrollment.last_watched_video_id = video_id
        enrollment.last_position_seconds = position

    # Recalculate progress
    total_videos = enrollment.course.videos.filter_by(is_active=True).count()
    completed = len(enrollment.completed_videos_json or [])
    enrollment.progress_percentage = (completed / total_videos * 100) if total_videos > 0 else 0

    if enrollment.progress_percentage >= enrollment.course.certificate_threshold:
        enrollment.status = "completed"
        enrollment.completed_at = datetime.utcnow()

    db.session.commit()
    return jsonify({"progress": enrollment.progress_percentage})

@api_bp.route("/courses/<int:course_id>/video/<int:video_id>/complete", methods=["POST"])
@login_required
def mark_video_complete(course_id, video_id):
    """Mark video as completed"""
    enrollment = CourseEnrollment.query.filter_by(
        user_id=current_user.id,
        course_id=course_id
    ).first_or_404()

    completed = enrollment.completed_videos_json or []
    if video_id not in completed:
        completed.append(video_id)
        enrollment.completed_videos_json = completed

        # Check if course completed
        total_videos = enrollment.course.videos.filter_by(is_active=True).count()
        enrollment.progress_percentage = (len(completed) / total_videos * 100) if total_videos > 0 else 0

        if enrollment.progress_percentage >= enrollment.course.certificate_threshold:
            enrollment.status = "completed"
            enrollment.completed_at = datetime.utcnow()
            # Generate certificate
            from app.helpers.certificate_generator import generate_certificate_pdf
            try:
                generate_certificate_pdf(enrollment)
            except Exception as e:
                current_app.logger.error(f"Certificate generation failed: {e}")

    db.session.commit()
    return jsonify({"success": True, "progress": enrollment.progress_percentage})
```

---

## 8. Database Migration

```python
# migrations/versions/xxx_add_video_courses.py

from alembic import op
import sqlalchemy as sa

def upgrade():
    # Course Categories
    op.create_table('course_categories',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('name_ar', sa.String(255), nullable=False),
        sa.Column('slug', sa.String(255), nullable=False, unique=True),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('description_ar', sa.Text(), nullable=True),
        sa.Column('icon', sa.String(50), nullable=True),
        sa.Column('color', sa.String(20), default='#D2AF20'),
        sa.Column('display_order', sa.Integer(), default=0),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )

    # Trainers
    op.create_table('trainers',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('name_ar', sa.String(255), nullable=True),
        sa.Column('bio', sa.Text(), nullable=True),
        sa.Column('bio_ar', sa.Text(), nullable=True),
        sa.Column('title', sa.String(255), nullable=True),
        sa.Column('title_ar', sa.String(255), nullable=True),
        sa.Column('avatar_url', sa.String(500), nullable=True),
        sa.Column('email', sa.String(255), nullable=True),
        sa.Column('linkedin_url', sa.String(500), nullable=True),
        sa.Column('twitter_url', sa.String(500), nullable=True),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )

    # Certificate Templates
    op.create_table('certificate_templates',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('name_ar', sa.String(255), nullable=False),
        sa.Column('background_image', sa.String(500), nullable=False),
        sa.Column('logo_position', sa.String(20), default='top'),
        sa.Column('text_color', sa.String(20), default='#1a1a1a'),
        sa.Column('accent_color', sa.String(20), default='#D2AF20'),
        sa.Column('font_family', sa.String(100), default='Amiri'),
        sa.Column('title_text', sa.String(255), default='شهادة إتمام'),
        sa.Column('title_text_ar', sa.String(255), default='شهادة إتمام'),
        sa.Column('body_template', sa.Text(), default='...'),
        sa.Column('body_template_ar', sa.Text(), nullable=True),
        sa.Column('signature_text', sa.String(255), nullable=True),
        sa.Column('signature_image', sa.String(500), nullable=True),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )

    # Courses
    op.create_table('courses',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('category_id', sa.Integer(), sa.ForeignKey('course_categories.id'), nullable=False),
        sa.Column('trainer_id', sa.Integer(), sa.ForeignKey('trainers.id'), nullable=False),
        sa.Column('title', sa.String(500), nullable=False),
        sa.Column('title_ar', sa.String(500), nullable=False),
        sa.Column('slug', sa.String(255), nullable=False, unique=True),
        sa.Column('subtitle', sa.String(500), nullable=True),
        sa.Column('subtitle_ar', sa.String(500), nullable=True),
        sa.Column('description', sa.Text(), nullable=False),
        sa.Column('description_ar', sa.Text(), nullable=False),
        sa.Column('cover_image', sa.String(500), nullable=True),
        sa.Column('promo_video_url', sa.String(500), nullable=True),
        sa.Column('level', sa.String(50), default='beginner'),
        sa.Column('language', sa.String(10), default='ar'),
        sa.Column('duration_hours', sa.Float(), default=0),
        sa.Column('price', sa.Float(), default=0),
        sa.Column('currency', sa.String(10), default='SAR'),
        sa.Column('is_free', sa.Boolean(), default=False),
        sa.Column('max_students', sa.Integer(), nullable=True),
        sa.Column('current_enrollments', sa.Integer(), default=0),
        sa.Column('requirements_json', sa.JSON(), default=list),
        sa.Column('requirements_ar_json', sa.JSON(), default=list),
        sa.Column('learning_objectives_json', sa.JSON(), default=list),
        sa.Column('learning_objectives_ar_json', sa.JSON(), default=list),
        sa.Column('target_audience_json', sa.JSON(), default=list),
        sa.Column('target_audience_ar_json', sa.JSON(), default=list),
        sa.Column('certificate_template_id', sa.Integer(), sa.ForeignKey('certificate_templates.id'), nullable=True),
        sa.Column('certificate_threshold', sa.Float(), default=80.0),
        sa.Column('status', sa.String(20), default='draft'),
        sa.Column('featured', sa.Boolean(), default=False),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('meta_title', sa.String(255), nullable=True),
        sa.Column('meta_description', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('updated_at', sa.DateTime(), default=sa.func.utcnow(), onupdate=sa.func.utcnow()),
        sa.Column('published_at', sa.DateTime(), nullable=True),
    )

    # Course Videos
    op.create_table('course_videos',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('course_id', sa.Integer(), sa.ForeignKey('courses.id'), nullable=False),
        sa.Column('title', sa.String(500), nullable=False),
        sa.Column('title_ar', sa.String(500), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('description_ar', sa.Text(), nullable=True),
        sa.Column('video_path', sa.String(500), nullable=False),
        sa.Column('video_url', sa.String(500), nullable=True),
        sa.Column('duration_seconds', sa.Integer(), default=0),
        sa.Column('thumbnail_path', sa.String(500), nullable=True),
        sa.Column('section_name', sa.String(255), nullable=True),
        sa.Column('section_name_ar', sa.String(255), nullable=True),
        sa.Column('position', sa.Integer(), default=0),
        sa.Column('is_preview', sa.Boolean(), default=False),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )

    # Course Enrollments
    op.create_table('course_enrollments',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('course_id', sa.Integer(), sa.ForeignKey('courses.id'), nullable=False),
        sa.Column('progress_percentage', sa.Float(), default=0),
        sa.Column('completed_videos_json', sa.JSON(), default=list),
        sa.Column('last_watched_video_id', sa.Integer(), sa.ForeignKey('course_videos.id'), nullable=True),
        sa.Column('last_position_seconds', sa.Integer(), default=0),
        sa.Column('status', sa.String(20), default='enrolled'),
        sa.Column('enrolled_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('completed_at', sa.DateTime(), nullable=True),
        sa.Column('certificate_issued', sa.Boolean(), default=False),
        sa.Column('certificate_id', sa.Integer(), sa.ForeignKey('certificates.id'), nullable=True),
    )

    # Certificates
    op.create_table('certificates',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('course_id', sa.Integer(), sa.ForeignKey('courses.id'), nullable=False),
        sa.Column('template_id', sa.Integer(), sa.ForeignKey('certificate_templates.id'), nullable=False),
        sa.Column('certificate_number', sa.String(100), nullable=False, unique=True),
        sa.Column('issued_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('verification_url', sa.String(500), nullable=True),
    )

    # Course Reviews
    op.create_table('course_reviews',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('course_id', sa.Integer(), sa.ForeignKey('courses.id'), nullable=False),
        sa.Column('rating', sa.Integer(), nullable=False),
        sa.Column('comment', sa.Text(), nullable=True),
        sa.Column('is_approved', sa.Boolean(), default=False),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
    )

    # Indexes
    op.create_index('idx_courses_category', 'courses', ['category_id'])
    op.create_index('idx_courses_trainer', 'courses', ['trainer_id'])
    op.create_index('idx_courses_status', 'courses', ['status'])
    op.create_index('idx_course_videos_course', 'course_videos', ['course_id'])
    op.create_index('idx_enrollments_user', 'course_enrollments', ['user_id'])
    op.create_index('idx_enrollments_course', 'course_enrollments', ['course_id'])

def downgrade():
    op.drop_table('course_reviews')
    op.drop_table('certificates')
    op.drop_table('course_enrollments')
    op.drop_table('course_videos')
    op.drop_table('courses')
    op.drop_table('certificate_templates')
    op.drop_table('trainers')
    op.drop_table('course_categories')
```

---

## 9. UI Templates Design

### 9.1 Admin Dashboard Components
- Course list table with search/filter
- Bulk upload drag-and-drop interface
- Video playlist editor with drag-drop reordering
- Certificate template editor with live preview

### 9.2 User-Facing Pages
- Course catalog with category filters
- Course landing page with curriculum preview
- Video player with sidebar navigation
- Progress tracking visualization
- Certificate view/download page

---

## 10. Implementation Order

### Phase 1: Foundation (Models & Database)
1. Create `app/models/course.py` with all models
2. Generate and run migration
3. Test model relationships

### Phase 2: Admin Basic CRUD
1. Categories management
2. Trainers management
3. Certificate templates
4. Basic courses CRUD

### Phase 3: Video Management
1. Single video upload
2. Bulk video upload interface
3. Video editing/reordering
4. Thumbnail generation

### Phase 4: User Enrollment & Learning
1. Course catalog pages
2. Course detail pages
3. Enrollment flow
4. Video player interface
5. Progress tracking

### Phase 5: Certificates
1. Certificate generation
2. Certificate templates
3. Certificate display/download
4. Public verification page

### Phase 6: Reviews & Analytics
1. Course reviews
2. Admin analytics dashboard
3. Student progress reports

---

## 11. Dependencies to Add

```txt
# requirements.txt additions
moviepy>=1.0.3          # Video thumbnail extraction
weasyprint>=60.0        # PDF certificate generation
pillow>=10.0.0          # Image processing
```

---

## 12. Security Considerations

1. **File Upload Validation**
   - Validate video file types (magic bytes, not just extension)
   - Limit file sizes
   - Sanitize filenames
   - Store outside webroot with proper serving

2. **Access Control**
   - `@login_required` for all learning pages
   - Verify enrollment before allowing video access
   - Rate limiting on enrollment endpoints

3. **Certificate Security**
   - Unique certificate numbers with UUID
   - Public verification endpoint
   - QR code on certificate for easy verification

---

## 13. Configuration Updates

```python
# app/config.py additions

class Config:
    # Video Upload Settings
    MAX_VIDEO_SIZE = 500 * 1024 * 1024  # 500MB
    ALLOWED_VIDEO_EXTENSIONS = {'mp4', 'webm', 'mov'}
    VIDEO_CHUNK_SIZE = 8 * 1024 * 1024  # For large uploads

    # Course Settings
    CERTIFICATE_THRESHOLD_DEFAULT = 80.0  # % to earn certificate
    MAX_ENROLLMENTS_PER_COURSE_DEFAULT = None  # Unlimited

    # Storage Paths
    COURSES_UPLOAD_PATH = 'uploads/courses'
    VIDEO_STORAGE_PATH = 'uploads/courses/videos'
    THUMBNAIL_STORAGE_PATH = 'uploads/courses/thumbnails'
    CERTIFICATE_STORAGE_PATH = 'uploads/courses/certificates'
```

---

## 14. Notes & Considerations

1. **Video Hosting**: For production, consider using CDN (AWS S3 + CloudFront, Cloudflare R2, or dedicated video hosting like Vimeo Pro) instead of local storage

2. **Payment Integration**: For paid courses, integrate with existing Stripe billing or add payment flow

3. **Streaming**: For large videos, implement HLS/DASH streaming for better performance

4. **Mobile**: Ensure video player is mobile-responsive with proper controls

5. **Localization**: All models have bilingual support (ar/en)

6. **Email Notifications**: Add enrollment confirmations, completion emails, certificate emails

7. **SEO**: Add structured data for courses (Schema.org Course type)