Video Course Platform Implementation Plan

Project: Coursera-like Video Tutorial Platform Technology: YouTube Unlisted Videos + Supabase + React Created: 2025-10-30 Status: Planning Phase

---

Table of Contents

1. Executive Summary
2. Database Schema Design
3. Admin Panel Architecture
4. Student Dashboard Architecture
5. YouTube Integration Strategy
6. Implementation Roadmap
7. Key API Endpoints
8. Security Considerations
9. Cost Analysis
10. Example Data Models
11. Decision Points
12. Progress Tracker

---

Executive Summary

Building a Coursera-like video tutorial platform using YouTube unlisted videos with existing Supabase infrastructure. The platform will have an Admin Panel for course management and a Student Dashboard for learning progress tracking.

Why YouTube Unlisted?

Pros:

• ✅ Free hosting & bandwidth
• ✅ Free transcoding (all quality variants)
• ✅ Global CDN delivery
• ✅ Adaptive streaming
• ✅ Mobile optimization
• ✅ Subtitles/Captions support
• ✅ Speed controls (0.5x to 2x)
• ✅ 99.9% uptime

Cost Savings: $200-500/month vs self-hosted solution

Cons:

• ⚠️ YouTube branding
• ⚠️ Limited player customization
• ⚠️ Potential takedowns (rare)
• ⚠️ URL can be shared

---

1. Database Schema Design

Current State Analysis

Existing tables:

• nova_tracks (courses)
• nova_modules (course content - text only)
• course_enrollments
• course_progress
• course_resources

New Tables Needed

1.1 Extend Existing nova_tracks Table

-- Add columns for video course features
ALTER TABLE nova_tracks ADD COLUMN IF NOT EXISTS
  instructor_name TEXT,
  thumbnail_url TEXT,
  trailer_youtube_id TEXT,
  total_lessons INTEGER DEFAULT 0,
  estimated_hours DECIMAL(4,1);

1.2 Course Sections Table

-- COURSE SECTIONS (Modules/Chapters)
CREATE TABLE course_sections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  course_id UUID NOT NULL REFERENCES nova_tracks(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  position INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(course_id, position)
);

CREATE INDEX idx_sections_course ON course_sections(course_id);

1.3 Course Lessons Table (VIDEO CONTENT)

-- LESSONS (Video Content)
CREATE TABLE course_lessons (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  section_id UUID NOT NULL REFERENCES course_sections(id) ON DELETE CASCADE,
  course_id UUID NOT NULL REFERENCES nova_tracks(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  youtube_video_id TEXT NOT NULL,  -- "dQw4w9WgXcQ" from youtube.com/watch?v=dQw4w9WgXcQ
  duration_seconds INTEGER,         -- Auto-extracted from YouTube API
  position INTEGER NOT NULL DEFAULT 0,
  is_preview BOOLEAN DEFAULT FALSE, -- Allow non-enrolled users to watch
  resources_text TEXT,              -- Additional notes/resources
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(section_id, position)
);

CREATE INDEX idx_lessons_course ON course_lessons(course_id);
CREATE INDEX idx_lessons_section ON course_lessons(section_id);

1.4 Lesson Progress Table

-- LESSON PROGRESS (Track video completion)
CREATE TABLE lesson_progress (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  lesson_id UUID NOT NULL REFERENCES course_lessons(id) ON DELETE CASCADE,
  enrollment_id UUID NOT NULL REFERENCES course_enrollments(id) ON DELETE CASCADE,
  watch_time_seconds INTEGER DEFAULT 0,
  completed BOOLEAN DEFAULT FALSE,
  last_watched_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, lesson_id)
);

CREATE INDEX idx_progress_user ON lesson_progress(user_id);
CREATE INDEX idx_progress_lesson ON lesson_progress(lesson_id);
CREATE INDEX idx_progress_enrollment ON lesson_progress(enrollment_id);

1.5 Lesson Notes Table

-- LESSON NOTES (Student's personal notes per lesson)
CREATE TABLE lesson_notes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  lesson_id UUID NOT NULL REFERENCES course_lessons(id) ON DELETE CASCADE,
  note_text TEXT NOT NULL,
  timestamp_seconds INTEGER, -- Optional: note at specific video timestamp
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_notes_user_lesson ON lesson_notes(user_id, lesson_id);

1.6 Quiz Questions Table (Optional)

-- QUIZ QUESTIONS (Optional for assessments)
CREATE TABLE quiz_questions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lesson_id UUID REFERENCES course_lessons(id) ON DELETE CASCADE,
  section_id UUID REFERENCES course_sections(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  question_type TEXT CHECK (question_type IN ('multiple_choice', 'true_false', 'short_answer')),
  options JSONB, -- {choices: ["A", "B", "C"], correct: 0}
  position INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

1.7 Admin Users Table

-- ADMIN USERS (Role-based access)
CREATE TABLE admin_users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT CHECK (role IN ('super_admin', 'course_admin', 'instructor')),
  permissions JSONB DEFAULT '{"can_create_courses": true, "can_edit_all": false}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id)
);

CREATE INDEX idx_admin_user ON admin_users(user_id);

1.8 Course Certificates Table

-- COURSE CERTIFICATES
CREATE TABLE course_certificates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  course_id UUID NOT NULL REFERENCES nova_tracks(id) ON DELETE CASCADE,
  certificate_code TEXT UNIQUE NOT NULL,
  issued_at TIMESTAMPTZ DEFAULT NOW(),
  completion_percentage DECIMAL(5,2) DEFAULT 100.00,
  UNIQUE(user_id, course_id)
);

CREATE INDEX idx_certificates_user ON course_certificates(user_id);
CREATE INDEX idx_certificates_code ON course_certificates(certificate_code);

1.9 Additional Indexes

-- Optimize enrollment queries
CREATE INDEX idx_enrollments_user ON course_enrollments(user_id);
CREATE INDEX idx_enrollments_course ON course_enrollments(course_id);
CREATE INDEX idx_enrollments_status ON course_enrollments(status);

Database Migration File

File to create: supabase/migrations/20251030000000_video_courses.sql

---

2. Admin Panel Architecture

2.1 Routes Structure

/admin
  /dashboard           - Overview stats (total courses, students, revenue)
  /courses             - List all courses (grid/table view)
  /courses/new         - Create new course form
  /courses/:id/edit    - Edit course details
  /courses/:id/sections - Manage sections (add/edit/delete/reorder)
  /courses/:id/lessons  - Manage lessons (add YouTube videos)
  /students            - View enrolled students
  /students/:id        - Individual student progress
  /analytics           - Course performance metrics
  /settings            - Admin settings

2.2 Admin Panel Features

A. Course Management

1. CREATE COURSE

• Basic info (title, description, instructor)
• Upload thumbnail (or use YouTube thumbnail)
• Set pricing tier (free/paid)
• Add YouTube trailer (unlisted)
• Set course status (draft/published)

2. MANAGE SECTIONS

• Add/Edit/Delete sections
• Drag-and-drop reordering
• Collapse/Expand UI for better organization
• Bulk operations

3. ADD LESSONS (VIDEO UPLOAD WORKFLOW)

Admin Input: YouTube unlisted URL
  ↓
Parse video ID from URL
  ↓
Call YouTube Data API to fetch:
  - Video title
  - Duration
  - Thumbnail
  - Description
  ↓
Display in form for admin to edit metadata
  ↓
Set lesson position (auto or manual)
  ↓
Mark as "preview" (free) or locked
  ↓
Save to database

4. BULK OPERATIONS

• Import lessons from CSV
• Bulk delete lessons
• Bulk reorder
• Bulk update settings (all lessons in section)

5. PREVIEW MODE

• View course as student would see it
• Test video playback
• Check lesson locking logic

B. Student Management

Features:

• View all enrolled students
• Filter by course/status
• Export enrollment data (CSV)
• Manual enrollment/unenrollment
• Send course announcement emails
• View individual student progress

C. Analytics Dashboard

Metrics to Display:

• Total enrollments (all time, this month)
• Course completion rates
• Average watch time per course
• Most popular lessons
• Drop-off points (where students quit)
• Daily/weekly active users
• Revenue (if paid courses)

2.3 Admin UI Component Hierarchy

<AdminLayout>
  <AdminSidebar>
    - Dashboard
    - Courses
    - Students
    - Analytics
    - Settings
  </AdminSidebar>

  <AdminHeader>
    - Breadcrumbs
    - User menu
    - Notifications
  </AdminHeader>

  <AdminContent>
    Routes:
    - <AdminDashboard />
    - <CourseList />
    - <CourseEditor />
    - <SectionManager />
    - <LessonManager />
    - <StudentTable />
    - <AnalyticsDashboard />
  </AdminContent>
</AdminLayout>

2.4 Key Admin Components

Components to create:

components/admin/
  ├── AdminLayout.jsx
  ├── AdminSidebar.jsx
  ├── AdminHeader.jsx
  ├── CourseList.jsx
  ├── CourseEditor.jsx
  ├── SectionManager.jsx
  ├── LessonManager.jsx
  ├── YouTubeVideoInput.jsx      ← Key component for video upload
  ├── LessonForm.jsx
  ├── DragDropReorder.jsx
  ├── StudentTable.jsx
  ├── StudentProgress.jsx
  ├── AnalyticsDashboard.jsx
  ├── AnalyticsChart.jsx
  └── BulkActions.jsx

pages/admin/
  ├── Dashboard.jsx
  ├── CourseList.jsx
  ├── CourseEditor.jsx
  ├── Students.jsx
  └── Analytics.jsx

src/services/
  ├── adminCourses.js
  ├── adminStudents.js
  └── adminAnalytics.js

---

3. Student Dashboard Architecture

3.1 Routes Structure

/dashboard
  /                        - My courses overview
  /courses/:slug           - Course detail page (curriculum preview)
  /courses/:slug/learn     - Video player + lesson list (main learning page)
  /courses/:slug/learn/:lessonId  - Specific lesson
  /progress                - Overall progress tracking
  /certificates            - Earned certificates
  /settings                - Profile settings

3.2 Dashboard Features

A. My Courses View

Display:

• Enrolled courses (grid or list view)
• Progress bars (% complete per course)
• "Continue watching" button (resumes last lesson)
• Recently added courses
• Recommended courses based on completed courses
• Filter by: All / In Progress / Completed

Data Structure Per Course:

{
  course: {
    id: "uuid",
    title: "Course Name",
    slug: "course-name",
    thumbnail_url: "...",
    instructor_name: "...",
    total_lessons: 24,
    estimated_hours: 8.5
  },
  enrollment: {
    enrolled_at: "2025-10-01",
    status: "active"
  },
  progress: {
    completed_lessons: 5,
    total_lessons: 24,
    percentage: 20.8,
    last_watched_lesson: {
      id: "uuid",
      title: "Lesson 5",
      section_title: "Module 2"
    },
    last_watched_at: "2025-10-29"
  }
}

B. Course Learning Page (Main Video Player)

Layout:

┌─────────────────────────────────────────────────────────┐
│  Course Header                                          │
│  Course Title | Progress: 5/24 (20%) | Certificate      │
├──────────────┬──────────────────────────────────────────┤
│              │                                          │
│  LESSON      │   VIDEO PLAYER                           │
│  SIDEBAR     │   (YouTube Embed - 16:9 responsive)      │
│              │                                          │
│ □ Intro      │   ─────────────────────────────────     │
│   ✓ 1.1      │   Current Lesson: "Introduction to AI"  │
│   ⏸ 1.2      │   Duration: 12:30                       │
│   ○ 1.3      │                                          │
│   🔒 1.4      │   ─────────────────────────────────     │
│              │                                          │
│ □ Module 2   │   📄 Resources:                          │
│   ○ 2.1      │   - Download slides.pdf                 │
│   ○ 2.2      │   - Code samples (GitHub)               │
│              │                                          │
│              │   📝 My Notes:                           │
│              │   [Add a note at 5:23...]              │
│              │                                          │
│              │   - Note at 2:15: "Key concept here"    │
│              │   - Note at 8:40: "Practice this"       │
│              │                                          │
└──────────────┴──────────────────────────────────────────┘

Features:

• ✓ = Completed (green checkmark)
• ⏸ = In Progress (blue)
• 🔒 = Locked (if sequential learning enabled)
• ○ = Not started (gray)
• Auto-mark complete at 90% watch time
• Continue where you left off (resume playback)
• Auto-advance to next lesson option
• Keyboard shortcuts (Space = play/pause, Arrow keys = seek)
• Fullscreen mode
• Playback speed control (inherited from YouTube)

C. Progress Tracking Page

Display:

• Overall completion stats (pie chart)
• Time spent learning (total hours)
• Certificates earned (badge gallery)
• Streak tracking (days active)
• Milestones achieved:
  • 🎯 First lesson completed
  • 🔥 10 lessons completed
  • 💪 50 lessons completed
  • 🏆 First course completed
  • 🌟 5 courses completed

SQL Query for Stats:

SELECT
  COUNT(DISTINCT ce.course_id) as courses_enrolled,
  COUNT(DISTINCT CASE WHEN lp.completed = TRUE THEN lp.lesson_id END) as lessons_completed,
  COUNT(DISTINCT CASE WHEN cc.id IS NOT NULL THEN cc.course_id END) as courses_completed,
  SUM(lp.watch_time_seconds) as total_watch_time_seconds
FROM course_enrollments ce
LEFT JOIN lesson_progress lp ON lp.user_id = ce.user_id
LEFT JOIN course_certificates cc ON cc.user_id = ce.user_id AND cc.course_id = ce.course_id
WHERE ce.user_id = $1

3.3 Student UI Components

Components to create:

components/dashboard/
  ├── DashboardLayout.jsx
  ├── DashboardSidebar.jsx
  ├── CourseCard.jsx
  ├── CourseGrid.jsx
  ├── ProgressBar.jsx
  ├── ProgressCircle.jsx
  ├── LessonSidebar.jsx
  ├── LessonItem.jsx
  ├── VideoPlayer.jsx            ← Key component
  ├── VideoControls.jsx
  ├── LessonNotes.jsx
  ├── NoteForm.jsx
  ├── ResourcesList.jsx
  ├── CertificateCard.jsx
  └── MilestonesBadge.jsx

pages/dashboard/
  ├── MyCourses.jsx
  ├── CourseDetail.jsx
  ├── LearningPage.jsx           ← Main video learning page
  ├── Progress.jsx
  ├── Certificates.jsx
  └── Settings.jsx

src/services/
  ├── studentCourses.js
  ├── lessonProgress.js
  ├── certificates.js
  └── notes.js

---

4. YouTube Integration Strategy

4.1 YouTube Data API Setup

Get API Key:

1. Go to Google Cloud Console
2. Create new project
3. Enable "YouTube Data API v3"
4. Create credentials (API Key)
5. Add to .env: VITE_YOUTUBE_API_KEY=your_key_here

API Quota: 10,000 units/day (each video metadata request = 1 unit)

4.2 YouTube Service

File: src/services/youtube.js

const YOUTUBE_API_KEY = import.meta.env.VITE_YOUTUBE_API_KEY;
const YOUTUBE_API_BASE = 'https://www.googleapis.com/youtube/v3';

/**
 * Extract video ID from YouTube URL
 * Supports:
 * - https://www.youtube.com/watch?v=VIDEO_ID
 * - https://youtu.be/VIDEO_ID
 * - https://www.youtube.com/embed/VIDEO_ID
 */
export function extractVideoId(url) {
  const patterns = [
    /(?:youtube\.com\/watch\?v=|youtu\.be\/|youtube\.com\/embed\/)([a-zA-Z0-9_-]{11})/,
    /^([a-zA-Z0-9_-]{11})$/ // Already just an ID
  ];

  for (const pattern of patterns) {
    const match = url.match(pattern);
    if (match) return match[1];
  }

  return null;
}

/**
 * Validate YouTube video ID format
 */
export function validateYouTubeId(id) {
  return /^[a-zA-Z0-9_-]{11}$/.test(id);
}

/**
 * Fetch video metadata from YouTube Data API
 */
export async function getVideoMetadata(videoId) {
  if (!validateYouTubeId(videoId)) {
    throw new Error('Invalid YouTube video ID format');
  }

  const url = `${YOUTUBE_API_BASE}/videos?id=${videoId}&part=snippet,contentDetails&key=${YOUTUBE_API_KEY}`;

  const response = await fetch(url);
  const data = await response.json();

  if (data.error) {
    throw new Error(`YouTube API Error: ${data.error.message}`);
  }

  if (!data.items || data.items.length === 0) {
    throw new Error('Video not found or is private');
  }

  const video = data.items[0];

  return {
    id: videoId,
    title: video.snippet.title,
    description: video.snippet.description,
    thumbnail: video.snippet.thumbnails.high?.url || video.snippet.thumbnails.default.url,
    duration_seconds: parseDuration(video.contentDetails.duration),
    channel: video.snippet.channelTitle,
    published_at: video.snippet.publishedAt,
  };
}

/**
 * Convert ISO 8601 duration to seconds
 * Example: PT1H2M10S -> 3730 seconds
 */
function parseDuration(isoDuration) {
  const match = isoDuration.match(/PT(?:(\d+)H)?(?:(\d+)M)?(?:(\d+)S)?/);

  if (!match) return 0;

  const hours = parseInt(match[1] || 0);
  const minutes = parseInt(match[2] || 0);
  const seconds = parseInt(match[3] || 0);

  return hours * 3600 + minutes * 60 + seconds;
}

/**
 * Format seconds to HH:MM:SS or MM:SS
 */
export function formatDuration(seconds) {
  const hours = Math.floor(seconds / 3600);
  const minutes = Math.floor((seconds % 3600) / 60);
  const secs = seconds % 60;

  if (hours > 0) {
    return `${hours}:${minutes.toString().padStart(2, '0')}:${secs.toString().padStart(2, '0')}`;
  }
  return `${minutes}:${secs.toString().padStart(2, '0')}`;
}

/**
 * Get YouTube embed URL
 */
export function getEmbedUrl(videoId, options = {}) {
  const params = new URLSearchParams({
    modestbranding: options.modestbranding ?? 1,
    rel: options.rel ?? 0,
    origin: window.location.origin,
    ...options
  });

  return `https://www.youtube.com/embed/${videoId}?${params.toString()}`;
}

4.3 Video Player Component

File: components/VideoPlayer.jsx

import { useEffect, useRef, useState } from 'react';

/**
 * YouTube IFrame API Video Player
 * Docs: https://developers.google.com/youtube/iframe_api_reference
 */
export function VideoPlayer({
  youtubeId,
  onProgress,
  onComplete,
  startTime = 0,
  autoplay = false
}) {
  const playerRef = useRef(null);
  const containerRef = useRef(null);
  const [isReady, setIsReady] = useState(false);

  useEffect(() => {
    // Load YouTube IFrame API
    if (!window.YT) {
      const tag = document.createElement('script');
      tag.src = 'https://www.youtube.com/iframe_api';
      document.body.appendChild(tag);
    }

    // Initialize player when API is ready
    window.onYouTubeIframeAPIReady = () => {
      playerRef.current = new window.YT.Player('youtube-player', {
        videoId: youtubeId,
        playerVars: {
          modestbranding: 1,      // Minimize YouTube branding
          rel: 0,                 // Don't show related videos at end
          origin: window.location.origin,
          start: startTime,       // Resume from last position
          autoplay: autoplay ? 1 : 0,
        },
        events: {
          onReady: handlePlayerReady,
          onStateChange: handleStateChange,
        },
      });
    };

    // If API already loaded
    if (window.YT && window.YT.Player) {
      window.onYouTubeIframeAPIReady();
    }

    return () => {
      if (playerRef.current?.destroy) {
        playerRef.current.destroy();
      }
    };
  }, [youtubeId]);

  const handlePlayerReady = (event) => {
    setIsReady(true);
    if (autoplay) {
      event.target.playVideo();
    }
  };

  const handleStateChange = (event) => {
    // Video ended
    if (event.data === window.YT.PlayerState.ENDED) {
      onComplete?.();
    }
  };

  // Track progress every 5 seconds
  useEffect(() => {
    if (!isReady) return;

    const interval = setInterval(() => {
      if (playerRef.current?.getCurrentTime && playerRef.current?.getDuration) {
        const currentTime = Math.floor(playerRef.current.getCurrentTime());
        const duration = Math.floor(playerRef.current.getDuration());

        if (duration > 0) {
          onProgress?.(currentTime, duration);
        }
      }
    }, 5000); // Update every 5 seconds

    return () => clearInterval(interval);
  }, [isReady, onProgress]);

  return (
    <div
      ref={containerRef}
      className="video-player-container"
      style={{
        position: 'relative',
        paddingBottom: '56.25%', // 16:9 aspect ratio
        height: 0,
        overflow: 'hidden'
      }}
    >
      <div
        id="youtube-player"
        style={{
          position: 'absolute',
          top: 0,
          left: 0,
          width: '100%',
          height: '100%'
        }}
      />
    </div>
  );
}

4.4 Progress Tracking Logic

File: src/services/lessonProgress.js

import { supabase } from '../lib/supabaseClient';

const COMPLETION_THRESHOLD = 0.9; // 90% watched = complete

/**
 * Update lesson progress for a user
 */
export async function updateLessonProgress(
  userId,
  lessonId,
  enrollmentId,
  watchTimeSeconds,
  totalDuration
) {
  const completionPercentage = watchTimeSeconds / totalDuration;
  const isCompleted = completionPercentage >= COMPLETION_THRESHOLD;

  const { data, error } = await supabase
    .from('lesson_progress')
    .upsert({
      user_id: userId,
      lesson_id: lessonId,
      enrollment_id: enrollmentId,
      watch_time_seconds: watchTimeSeconds,
      completed: isCompleted,
      last_watched_at: new Date().toISOString(),
      updated_at: new Date().toISOString(),
    }, {
      onConflict: 'user_id,lesson_id'
    })
    .select()
    .single();

  if (error) throw error;

  // If lesson just completed, check if course is complete
  if (isCompleted && data) {
    await checkCourseCompletion(userId, lessonId, enrollmentId);
  }

  return data;
}

/**
 * Check if user completed entire course
 */
async function checkCourseCompletion(userId, lessonId, enrollmentId) {
  // Get course ID from lesson
  const { data: lesson } = await supabase
    .from('course_lessons')
    .select('course_id')
    .eq('id', lessonId)
    .single();

  if (!lesson) return;

  // Count completed vs total lessons
  const { data: totalLessons } = await supabase
    .from('course_lessons')
    .select('id', { count: 'exact', head: true })
    .eq('course_id', lesson.course_id);

  const { data: completedLessons } = await supabase
    .from('lesson_progress')
    .select('id', { count: 'exact', head: true })
    .eq('user_id', userId)
    .eq('enrollment_id', enrollmentId)
    .eq('completed', true);

  const totalCount = totalLessons?.count || 0;
  const completedCount = completedLessons?.count || 0;

  // If 100% complete, issue certificate
  if (totalCount > 0 && completedCount >= totalCount) {
    await issueCertificate(userId, lesson.course_id);
  }
}

/**
 * Issue certificate for course completion
 */
async function issueCertificate(userId, courseId) {
  // Check if certificate already exists
  const { data: existing } = await supabase
    .from('course_certificates')
    .select('id')
    .eq('user_id', userId)
    .eq('course_id', courseId)
    .single();

  if (existing) return existing;

  // Generate unique certificate code
  const certificateCode = generateCertificateCode();

  const { data, error } = await supabase
    .from('course_certificates')
    .insert({
      user_id: userId,
      course_id: courseId,
      certificate_code: certificateCode,
      completion_percentage: 100.00,
    })
    .select()
    .single();

  if (error) throw error;

  // TODO: Send congratulations email

  return data;
}

/**
 * Generate unique certificate code
 */
function generateCertificateCode() {
  const timestamp = Date.now().toString(36).toUpperCase();
  const random = Math.random().toString(36).substring(2, 8).toUpperCase();
  return `CERT-${timestamp}-${random}`;
}

/**
 * Get user's progress for a specific course
 */
export async function getCourseProgress(userId, courseId) {
  const { data, error } = await supabase.rpc('get_course_progress', {
    p_user_id: userId,
    p_course_id: courseId
  });

  if (error) throw error;
  return data;
}

Database Function (add to migration):

-- Function to get course progress statistics
CREATE OR REPLACE FUNCTION get_course_progress(
  p_user_id UUID,
  p_course_id UUID
)
RETURNS TABLE(
  total_lessons INTEGER,
  completed_lessons INTEGER,
  in_progress_lessons INTEGER,
  total_duration_seconds INTEGER,
  watched_duration_seconds INTEGER,
  completion_percentage DECIMAL(5,2),
  last_watched_at TIMESTAMPTZ
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    COUNT(cl.id)::INTEGER as total_lessons,
    COUNT(lp.id) FILTER (WHERE lp.completed = TRUE)::INTEGER as completed_lessons,
    COUNT(lp.id) FILTER (WHERE lp.completed = FALSE AND lp.watch_time_seconds > 0)::INTEGER as in_progress_lessons,
    COALESCE(SUM(cl.duration_seconds), 0)::INTEGER as total_duration_seconds,
    COALESCE(SUM(lp.watch_time_seconds), 0)::INTEGER as watched_duration_seconds,
    CASE
      WHEN COUNT(cl.id) > 0 THEN
        (COUNT(lp.id) FILTER (WHERE lp.completed = TRUE)::DECIMAL / COUNT(cl.id) * 100)
      ELSE 0
    END as completion_percentage,
    MAX(lp.last_watched_at) as last_watched_at
  FROM course_lessons cl
  LEFT JOIN lesson_progress lp ON lp.lesson_id = cl.id AND lp.user_id = p_user_id
  WHERE cl.course_id = p_course_id;
END;
$$ LANGUAGE plpgsql;

---

5. Implementation Roadmap

Phase 1: Database Setup ✅ (Week 1)

Priority: HIGH Estimated Time: 3-5 days

Tasks:

• Create migration file supabase/migrations/20251030000000_video_courses.sql
• Add all new tables (course_sections, course_lessons, lesson_progress, etc.)
• Add indexes for performance
• Add Row Level Security policies
• Create database functions (get_course_progress, etc.)
• Test with sample data
• Verify RLS policies work correctly

Files to create:

• supabase/migrations/20251030000000_video_courses.sql

Testing Checklist:

• Can insert sample course
• Can insert sections and lessons
• Can track progress
• RLS prevents unauthorized access
• Indexes improve query performance

---

Phase 2: YouTube Integration ✅ (Week 1)

Priority: HIGH Estimated Time: 2-3 days

Tasks:

• Get YouTube Data API key from Google Cloud Console
• Add API key to environment variables
• Create src/services/youtube.js
• Implement video ID extraction
• Implement metadata fetching
• Test with various YouTube URL formats
• Create VideoPlayer component
• Test video embedding and playback

Files to create:

• src/services/youtube.js
• components/VideoPlayer.jsx
• src/hooks/useYouTubePlayer.js (optional)

Testing Checklist:

• Can extract video ID from different URL formats
• Can fetch video metadata (title, duration, thumbnail)
• Video player loads and plays correctly
• Progress tracking works during playback
• Video completes and triggers onComplete callback

---

Phase 3: Admin Panel - Core ✅ (Week 2-3)

Priority: HIGH Estimated Time: 7-10 days

Tasks:

Week 2:

• Set up admin routes with authentication
• Create AdminLayout component
• Create admin role checking middleware
• Build Dashboard page (basic stats)
• Build CourseList page (view all courses)
• Build CourseEditor page (create/edit course basic info)

Week 3:

• Build SectionManager component (add/edit/delete sections)
• Build LessonManager component
• Build YouTubeVideoInput component (paste URL, fetch metadata)
• Implement drag-and-drop reordering for sections/lessons
• Add delete confirmations
• Test full course creation workflow

Files to create:

pages/admin/
  ├── Dashboard.jsx
  ├── CourseList.jsx
  ├── CourseEditor.jsx
  └── CourseContent.jsx (sections + lessons)

components/admin/
  ├── AdminLayout.jsx
  ├── AdminSidebar.jsx
  ├── AdminHeader.jsx
  ├── CourseForm.jsx
  ├── SectionManager.jsx
  ├── SectionForm.jsx
  ├── LessonManager.jsx
  ├── LessonForm.jsx
  ├── YouTubeVideoInput.jsx
  └── DragDropReorder.jsx

src/services/
  ├── adminCourses.js
  ├── adminSections.js
  └── adminLessons.js

src/hooks/
  └── useAdminAuth.js

Testing Checklist:

• Can create new course
• Can add sections to course
• Can paste YouTube URL and fetch metadata
• Can add lesson with YouTube video
• Can reorder sections and lessons
• Can edit course/section/lesson
• Can delete course/section/lesson
• Only admin users can access /admin routes

---

Phase 4: Student Dashboard ✅ (Week 3-4)

Priority: HIGH Estimated Time: 7-10 days

Tasks:

Week 3:

• Create dashboard routes
• Build DashboardLayout component
• Build MyCourses page (show enrolled courses)
• Build CourseCard component with progress bar
• Implement enrollment flow

Week 4:

• Build CourseDetail page (curriculum overview)
• Build LearningPage (video player + lesson sidebar)
• Build LessonSidebar component
• Implement progress tracking (save to DB)
• Build LessonNotes component
• Build Progress page (stats and milestones)
• Test complete learning flow (enroll → watch → complete)

Files to create:

pages/dashboard/
  ├── MyCourses.jsx
  ├── CourseDetail.jsx
  ├── LearningPage.jsx
  ├── Progress.jsx
  └── Settings.jsx

components/dashboard/
  ├── DashboardLayout.jsx
  ├── DashboardSidebar.jsx
  ├── CourseCard.jsx
  ├── CourseGrid.jsx
  ├── ProgressBar.jsx
  ├── ProgressCircle.jsx
  ├── LessonSidebar.jsx
  ├── LessonItem.jsx
  ├── LessonNotes.jsx
  ├── NoteForm.jsx
  └── ResourcesList.jsx

src/services/
  ├── studentCourses.js
  ├── lessonProgress.js
  └── notes.js

src/hooks/
  ├── useCourseProgress.js
  ├── useLessonProgress.js
  └── useLessonNotes.js

Testing Checklist:

• Can view enrolled courses
• Can see progress percentage per course
• Can click course and see curriculum
• Can start watching lesson
• Progress updates every 5 seconds
• Lesson marks complete at 90% watched
• Can resume from last position
• Can add notes to lessons
• Progress page shows correct stats

---

Phase 5: Analytics & Polish ✅ (Week 4-5)

Priority: MEDIUM Estimated Time: 5-7 days

Tasks:

• Build admin analytics dashboard
• Add charts (enrollments over time, completion rates)
• Build StudentTable component (view all students)
• Build individual student progress view
• Add completion certificates
• Design certificate template
• Add email notifications (welcome, completion, etc.)
• Implement course search/filter
• Add drag-and-drop for reordering
• Mobile responsive optimization
• Performance optimization (lazy loading, caching)

Files to create:

pages/admin/
  ├── Analytics.jsx
  └── Students.jsx

components/admin/
  ├── AnalyticsDashboard.jsx
  ├── AnalyticsChart.jsx
  ├── StudentTable.jsx
  └── StudentProgress.jsx

pages/dashboard/
  └── Certificates.jsx

components/dashboard/
  ├── CertificateCard.jsx
  └── Certificate.jsx (printable)

src/services/
  ├── analytics.js
  ├── certificates.js
  └── email.js

Testing Checklist:

• Analytics show correct data
• Can view all enrolled students
• Can view individual student progress
• Certificate auto-issues on course completion
• Certificate is downloadable/printable
• Email notifications send correctly
• Search and filters work
• Mobile responsive on all pages

---

Phase 6: Advanced Features ✅ (Week 6+)

Priority: LOW (Nice to have) Estimated Time: 10-15 days

Tasks:

• Quiz system (create quiz questions, take quizzes)
• Discussion forums per lesson
• Downloadable resources (PDFs, code files)
• Course reviews and ratings
• Student-to-student messaging
• Live session integration (Zoom/Google Meet)
• Assignments and submissions
• Instructor feedback system
• Gamification (badges, leaderboards)
• Course prerequisites
• Drip content (unlock lessons over time)

Files to create:

components/quiz/
  ├── QuizQuestion.jsx
  ├── QuizResults.jsx
  └── QuizCreator.jsx

components/discussion/
  ├── DiscussionThread.jsx
  ├── CommentForm.jsx
  └── CommentList.jsx

components/resources/
  └── DownloadableResources.jsx

components/reviews/
  ├── ReviewForm.jsx
  └── ReviewList.jsx

---

6. Key API Endpoints Needed

6.1 Admin APIs (Service Role Key Required)

Create Course with Content

// src/services/adminCourses.js

export async function createCourseWithContent(courseData) {
  const { data: course, error: courseError } = await supabase
    .from('nova_tracks')
    .insert({
      slug: courseData.slug,
      code: courseData.code,
      title: courseData.title,
      description: courseData.description,
      instructor_name: courseData.instructor_name,
      thumbnail_url: courseData.thumbnail_url,
      trailer_youtube_id: courseData.trailer_youtube_id,
      pricing_tier: courseData.pricing_tier || 'free',
      status: courseData.status || 'open',
    })
    .select()
    .single();

  if (courseError) throw courseError;

  return course;
}

export async function updateCourse(courseId, updates) {
  const { data, error } = await supabase
    .from('nova_tracks')
    .update(updates)
    .eq('id', courseId)
    .select()
    .single();

  if (error) throw error;
  return data;
}

export async function deleteCourse(courseId) {
  const { error } = await supabase
    .from('nova_tracks')
    .delete()
    .eq('id', courseId);

  if (error) throw error;
}

Manage Sections

// src/services/adminSections.js

export async function createSection(sectionData) {
  const { data, error } = await supabase
    .from('course_sections')
    .insert({
      course_id: sectionData.course_id,
      title: sectionData.title,
      description: sectionData.description,
      position: sectionData.position,
    })
    .select()
    .single();

  if (error) throw error;
  return data;
}

export async function updateSection(sectionId, updates) {
  const { data, error } = await supabase
    .from('course_sections')
    .update(updates)
    .eq('id', sectionId)
    .select()
    .single();

  if (error) throw error;
  return data;
}

export async function deleteSection(sectionId) {
  const { error } = await supabase
    .from('course_sections')
    .delete()
    .eq('id', sectionId);

  if (error) throw error;
}

export async function reorderSections(courseId, sectionIds) {
  // Update position for each section
  const updates = sectionIds.map((sectionId, index) => ({
    id: sectionId,
    position: index,
  }));

  for (const update of updates) {
    await supabase
      .from('course_sections')
      .update({ position: update.position })
      .eq('id', update.id);
  }
}

Manage Lessons

// src/services/adminLessons.js

import { getVideoMetadata } from './youtube';

export async function createLessonFromYouTubeUrl(url, lessonData) {
  // Extract video ID and fetch metadata
  const videoId = extractVideoId(url);
  if (!videoId) throw new Error('Invalid YouTube URL');

  const metadata = await getVideoMetadata(videoId);

  // Insert lesson
  const { data, error } = await supabase
    .from('course_lessons')
    .insert({
      section_id: lessonData.section_id,
      course_id: lessonData.course_id,
      title: lessonData.title || metadata.title,
      description: lessonData.description || metadata.description,
      youtube_video_id: videoId,
      duration_seconds: metadata.duration_seconds,
      position: lessonData.position,
      is_preview: lessonData.is_preview || false,
    })
    .select()
    .single();

  if (error) throw error;

  // Update course total_lessons count
  await updateCourseLessonCount(lessonData.course_id);

  return data;
}

export async function updateLesson(lessonId, updates) {
  const { data, error } = await supabase
    .from('course_lessons')
    .update(updates)
    .eq('id', lessonId)
    .select()
    .single();

  if (error) throw error;
  return data;
}

export async function deleteLesson(lessonId) {
  const { data: lesson } = await supabase
    .from('course_lessons')
    .select('course_id')
    .eq('id', lessonId)
    .single();

  const { error } = await supabase
    .from('course_lessons')
    .delete()
    .eq('id', lessonId);

  if (error) throw error;

  // Update course total_lessons count
  if (lesson) {
    await updateCourseLessonCount(lesson.course_id);
  }
}

async function updateCourseLessonCount(courseId) {
  const { count } = await supabase
    .from('course_lessons')
    .select('id', { count: 'exact', head: true })
    .eq('course_id', courseId);

  await supabase
    .from('nova_tracks')
    .update({ total_lessons: count })
    .eq('id', courseId);
}

Admin Analytics

// src/services/adminAnalytics.js

export async function getAdminStats() {
  // Total courses
  const { count: totalCourses } = await supabase
    .from('nova_tracks')
    .select('id', { count: 'exact', head: true });

  // Total enrollments
  const { count: totalEnrollments } = await supabase
    .from('course_enrollments')
    .select('id', { count: 'exact', head: true });

  // Total certificates issued
  const { count: totalCertificates } = await supabase
    .from('course_certificates')
    .select('id', { count: 'exact', head: true });

  // Total students (unique users enrolled)
  const { data: uniqueStudents } = await supabase
    .from('course_enrollments')
    .select('user_id');

  return {
    totalCourses,
    totalEnrollments,
    totalCertificates,
    totalStudents: new Set(uniqueStudents?.map(e => e.user_id)).size,
  };
}

export async function getCourseAnalytics(courseId) {
  const { data, error } = await supabase.rpc('get_course_analytics', {
    p_course_id: courseId
  });

  if (error) throw error;
  return data;
}

Add to migration:

-- Analytics function
CREATE OR REPLACE FUNCTION get_course_analytics(p_course_id UUID)
RETURNS TABLE(
  total_enrollments BIGINT,
  active_enrollments BIGINT,
  completed_enrollments BIGINT,
  avg_completion_percentage DECIMAL(5,2),
  total_watch_time_hours DECIMAL(10,2)
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    COUNT(ce.id) as total_enrollments,
    COUNT(ce.id) FILTER (WHERE ce.status = 'active') as active_enrollments,
    COUNT(cc.id) as completed_enrollments,
    AVG(
      (SELECT COUNT(*) FILTER (WHERE lp.completed = TRUE)::DECIMAL
       FROM lesson_progress lp
       WHERE lp.enrollment_id = ce.id)
      / NULLIF((SELECT COUNT(*) FROM course_lessons WHERE course_id = p_course_id), 0)
      * 100
    ) as avg_completion_percentage,
    COALESCE(SUM(
      (SELECT SUM(lp.watch_time_seconds)
       FROM lesson_progress lp
       WHERE lp.enrollment_id = ce.id)
    ) / 3600.0, 0) as total_watch_time_hours
  FROM course_enrollments ce
  LEFT JOIN course_certificates cc ON cc.user_id = ce.user_id AND cc.course_id = ce.course_id
  WHERE ce.course_id = p_course_id;
END;
$$ LANGUAGE plpgsql;

6.2 Student APIs (RLS Protected)

Get Enrolled Courses with Progress

// src/services/studentCourses.js

export async function getMyCoursesWithProgress(userId) {
  const { data, error } = await supabase
    .from('course_enrollments')
    .select(`
      id,
      enrolled_at: created_at,
      status,
      course:nova_tracks (
        id,
        slug,
        title,
        description,
        instructor_name,
        thumbnail_url,
        total_lessons,
        estimated_hours
      )
    `)
    .eq('user_id', userId)
    .order('created_at', { ascending: false });

  if (error) throw error;

  // Fetch progress for each course
  const coursesWithProgress = await Promise.all(
    data.map(async (enrollment) => {
      const progress = await getCourseProgress(userId, enrollment.course.id);

      // Get last watched lesson
      const { data: lastLesson } = await supabase
        .from('lesson_progress')
        .select(`
          lesson:course_lessons (
            id,
            title,
            section:course_sections (title)
          ),
          last_watched_at
        `)
        .eq('user_id', userId)
        .order('last_watched_at', { ascending: false })
        .limit(1)
        .single();

      return {
        enrollment,
        course: enrollment.course,
        progress: {
          ...progress,
          last_watched_lesson: lastLesson?.lesson,
          last_watched_at: lastLesson?.last_watched_at,
        },
      };
    })
  );

  return coursesWithProgress;
}

export async function getCourseProgress(userId, courseId) {
  const { data, error } = await supabase.rpc('get_course_progress', {
    p_user_id: userId,
    p_course_id: courseId
  });

  if (error) throw error;
  return data;
}

Get Course Curriculum

export async function getCourseCurriculum(courseId, userId = null) {
  // Get sections with lessons
  const { data: sections, error } = await supabase
    .from('course_sections')
    .select(`
      id,
      title,
      description,
      position,
      lessons:course_lessons (
        id,
        title,
        description,
        youtube_video_id,
        duration_seconds,
        position,
        is_preview
      )
    `)
    .eq('course_id', courseId)
    .order('position', { ascending: true });

  if (error) throw error;

  // If user is logged in, fetch their progress
  if (userId) {
    for (const section of sections) {
      for (const lesson of section.lessons) {
        const { data: progress } = await supabase
          .from('lesson_progress')
          .select('completed, watch_time_seconds')
          .eq('user_id', userId)
          .eq('lesson_id', lesson.id)
          .single();

        lesson.progress = progress || { completed: false, watch_time_seconds: 0 };
      }
    }
  }

  return sections;
}

Enroll in Course

export async function enrollInCourse(userId, courseId) {
  const { data, error } = await supabase
    .from('course_enrollments')
    .insert({
      user_id: userId,
      course_id: courseId,
      status: 'active',
      source: 'web-enroll',
    })
    .select()
    .single();

  if (error) {
    if (error.code === '23505') { // Unique violation
      throw new Error('You are already enrolled in this course');
    }
    throw error;
  }

  return data;
}

---

7. Security Considerations

7.1 Row Level Security (RLS) Policies

Add to migration file:

-- ============================================
-- RLS POLICIES FOR VIDEO COURSE PLATFORM
-- ============================================

-- Enable RLS on all tables
ALTER TABLE course_sections ENABLE ROW LEVEL SECURITY;
ALTER TABLE course_lessons ENABLE ROW LEVEL SECURITY;
ALTER TABLE lesson_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE lesson_notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE admin_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE course_certificates ENABLE ROW LEVEL SECURITY;

-- ============================================
-- ADMIN POLICIES
-- ============================================

-- Admin users: Full access to courses
CREATE POLICY "Admin full access courses"
  ON nova_tracks
  FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM admin_users
      WHERE user_id = auth.uid()
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM admin_users
      WHERE user_id = auth.uid()
    )
  );

-- Admin users: Full access to sections
CREATE POLICY "Admin full access sections"
  ON course_sections
  FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM admin_users
      WHERE user_id = auth.uid()
    )
  );

-- Admin users: Full access to lessons
CREATE POLICY "Admin full access lessons"
  ON course_lessons
  FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM admin_users
      WHERE user_id = auth.uid()
    )
  );

-- ============================================
-- STUDENT POLICIES - SECTIONS
-- ============================================

-- Students: View sections from enrolled courses
CREATE POLICY "Students view enrolled sections"
  ON course_sections
  FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM course_enrollments
      WHERE user_id = auth.uid()
        AND course_id = course_sections.course_id
        AND status = 'active'
    )
  );

-- Public: View sections from any course (for preview)
CREATE POLICY "Public view all sections"
  ON course_sections
  FOR SELECT
  TO public
  USING (true);

-- ============================================
-- STUDENT POLICIES - LESSONS
-- ============================================

-- Students: View lessons from enrolled courses OR preview lessons
CREATE POLICY "Students view enrolled lessons"
  ON course_lessons
  FOR SELECT
  TO authenticated
  USING (
    is_preview = TRUE  -- Allow previewing free lessons
    OR EXISTS (
      SELECT 1 FROM course_enrollments
      WHERE user_id = auth.uid()
        AND course_id = course_lessons.course_id
        AND status = 'active'
    )
  );

-- Public: View only preview lessons
CREATE POLICY "Public view preview lessons"
  ON course_lessons
  FOR SELECT
  TO public
  USING (is_preview = TRUE);

-- ============================================
-- STUDENT POLICIES - PROGRESS
-- ============================================

-- Users can only manage their own progress
CREATE POLICY "Users manage own progress"
  ON lesson_progress
  FOR ALL
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- Users can only view their own progress
CREATE POLICY "Users view own progress"
  ON lesson_progress
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

-- ============================================
-- STUDENT POLICIES - NOTES
-- ============================================

-- Users can only manage their own notes
CREATE POLICY "Users manage own notes"
  ON lesson_notes
  FOR ALL
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- ============================================
-- ADMIN USER POLICIES
-- ============================================

-- Only super admins can view admin users
CREATE POLICY "Super admins view admin users"
  ON admin_users
  FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM admin_users
      WHERE user_id = auth.uid()
        AND role = 'super_admin'
    )
  );

-- Only super admins can manage admin users
CREATE POLICY "Super admins manage admin users"
  ON admin_users
  FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM admin_users
      WHERE user_id = auth.uid()
        AND role = 'super_admin'
    )
  );

-- ============================================
-- CERTIFICATE POLICIES
-- ============================================

-- Users can view their own certificates
CREATE POLICY "Users view own certificates"
  ON course_certificates
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

-- Anyone can verify a certificate by code (public endpoint)
CREATE POLICY "Public verify certificates"
  ON course_certificates
  FOR SELECT
  TO public
  USING (true);

-- System can issue certificates (will use service role)
-- No policy needed - service role bypasses RLS

7.2 YouTube URL Validation

Security measures in YouTube service:

// src/services/youtube.js

/**
 * Validate YouTube video ID format (prevent XSS)
 */
export function validateYouTubeId(id) {
  // YouTube IDs are exactly 11 characters: letters, numbers, underscore, hyphen
  return /^[a-zA-Z0-9_-]{11}$/.test(id);
}

/**
 * Sanitize YouTube URL before processing
 */
export function sanitizeYouTubeUrl(url) {
  // Remove any script tags or suspicious content
  const cleaned = url.replace(/<script\b[^<]*(?:(?!<\/script>)<[^<]*)*<\/script>/gi, '');

  // Only allow youtube.com and youtu.be domains
  const allowedDomains = ['youtube.com', 'youtu.be', 'www.youtube.com'];

  try {
    const urlObj = new URL(cleaned);
    if (!allowedDomains.includes(urlObj.hostname)) {
      throw new Error('Invalid YouTube domain');
    }
    return cleaned;
  } catch {
    throw new Error('Invalid URL format');
  }
}

7.3 Admin Route Protection

File: src/hooks/useAdminAuth.js

import { useEffect, useState } from 'react';
import { useNavigate } from 'react-router-dom';
import { supabase } from '../lib/supabaseClient';

export function useAdminAuth() {
  const [isAdmin, setIsAdmin] = useState(false);
  const [isLoading, setIsLoading] = useState(true);
  const navigate = useNavigate();

  useEffect(() => {
    checkAdminStatus();
  }, []);

  async function checkAdminStatus() {
    try {
      // Get current user
      const { data: { user } } = await supabase.auth.getUser();

      if (!user) {
        navigate('/login');
        return;
      }

      // Check if user is admin
      const { data: adminUser } = await supabase
        .from('admin_users')
        .select('role')
        .eq('user_id', user.id)
        .single();

      if (!adminUser) {
        navigate('/'); // Redirect non-admins
        return;
      }

      setIsAdmin(true);
    } catch (error) {
      console.error('Admin auth check failed:', error);
      navigate('/');
    } finally {
      setIsLoading(false);
    }
  }

  return { isAdmin, isLoading };
}

Usage in admin pages:

// pages/admin/Dashboard.jsx
import { useAdminAuth } from '../../hooks/useAdminAuth';

export function AdminDashboard() {
  const { isAdmin, isLoading } = useAdminAuth();

  if (isLoading) return <div>Loading...</div>;
  if (!isAdmin) return null; // Will redirect

  return <div>Admin Dashboard Content</div>;
}

7.4 Environment Variables Security

Required environment variables:

# .env
VITE_SUPABASE_URL=https://your-project.supabase.co
VITE_SUPABASE_ANON_KEY=your-anon-key
VITE_YOUTUBE_API_KEY=your-youtube-api-key

Never commit:

• Service role keys (only use server-side)
• Production API keys
• Database passwords

---

8. Cost Analysis

8.1 Using YouTube Unlisted (Recommended)

Monthly Costs:

✅ Video Hosting: $0 (YouTube)
✅ Video Bandwidth: $0 (YouTube CDN)
✅ Video Transcoding: $0 (automatic)
✅ Video Storage: $0

💰 Supabase Pro: $25/month
   - 8 GB database
   - 100 GB bandwidth
   - 100 GB storage

💰 YouTube Data API: $0
   - 10,000 quota units/day (free)
   - ~100,000 API requests/month

💰 Domain: $12/year (~$1/month)

💰 Email Service (optional): $10-20/month
   - SendGrid, Mailgun, or Resend

TOTAL: $26-46/month

8.2 Self-Hosted Videos Alternative (NOT Recommended)

For comparison only:

💸 Cloudflare R2 Storage:
   - $0.015/GB/month storage
   - $0.01/GB egress (free tier: 10GB/month)
   - 100 courses @ 500MB/course = 50GB
   - Storage: $0.75/month
   - Bandwidth (1TB/month): $10/month

💸 Video Transcoding:
   - AWS MediaConvert: ~$0.015/minute
   - 100 courses @ 10 hours = 1,000 hours
   - One-time: $900
   - Ongoing for new videos: ~$50/month

💸 CDN (Cloudflare): $20/month

💸 Supabase: $25/month

TOTAL: ~$105/month + $900 upfront

🎯 YouTube saves you: $80-600/month!

8.3 Revenue Potential (If Paid Courses)

Example Pricing Model:

Free Tier:
- 10 courses
- Community support

Pro Tier ($29/month or $199/year):
- All courses
- Certificates
- Priority support
- Live Q&A sessions

Enterprise (Custom):
- Custom courses
- Team accounts
- Analytics

Projected Revenue (Year 1):
- 1,000 free users
- 100 pro monthly ($2,900/month)
- 50 pro yearly ($9,950 upfront)
- Total Year 1: ~$45,000

Costs Year 1: ~$500
Profit Margin: 98%+ 🚀

---

9. Example Data Models

9.1 Sample Course Structure

// COURSE
{
  id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
  slug: "ai-fundamentals",
  code: "AI101",
  title: "AI Fundamentals for Beginners",
  description: "Learn the basics of artificial intelligence and machine learning",
  instructor_name: "Dr. Jane Smith",
  thumbnail_url: "https://img.youtube.com/vi/VIDEO_ID/maxresdefault.jpg",
  trailer_youtube_id: "dQw4w9WgXcQ",
  total_lessons: 24,
  estimated_hours: 8.5,
  pricing_tier: "free",
  status: "open",
  created_at: "2025-10-01T00:00:00Z",
  updated_at: "2025-10-15T00:00:00Z"
}

9.2 Course Sections & Lessons

// SECTIONS
[
  {
    id: "section-uuid-1",
    course_id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    title: "Introduction to AI",
    description: "Get started with AI basics",
    position: 0,

    // LESSONS
    lessons: [
      {
        id: "lesson-uuid-1",
        section_id: "section-uuid-1",
        course_id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
        title: "What is AI?",
        description: "Understanding artificial intelligence",
        youtube_video_id: "abc123xyz",
        duration_seconds: 720,  // 12 minutes
        position: 0,
        is_preview: true,  // Free preview
        resources_text: "Download slides: https://example.com/slides.pdf"
      },
      {
        id: "lesson-uuid-2",
        section_id: "section-uuid-1",
        course_id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
        title: "History of AI",
        description: "From Turing to modern AI",
        youtube_video_id: "def456uvw",
        duration_seconds: 900,  // 15 minutes
        position: 1,
        is_preview: false,  // Locked for enrolled users only
        resources_text: null
      }
    ]
  },
  {
    id: "section-uuid-2",
    course_id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    title: "Machine Learning Basics",
    description: "Introduction to ML concepts",
    position: 1,
    lessons: [
      // More lessons...
    ]
  }
]

9.3 Student Progress Example

// USER ENROLLMENT
{
  id: "enrollment-uuid-1",
  user_id: "user-uuid-123",
  course_id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
  status: "active",
  source: "web-enroll",
  created_at: "2025-10-20T10:00:00Z"
}

// LESSON PROGRESS
[
  {
    id: "progress-uuid-1",
    user_id: "user-uuid-123",
    lesson_id: "lesson-uuid-1",
    enrollment_id: "enrollment-uuid-1",
    watch_time_seconds: 720,  // Watched full 12 minutes
    completed: true,
    last_watched_at: "2025-10-20T10:15:00Z"
  },
  {
    id: "progress-uuid-2",
    user_id: "user-uuid-123",
    lesson_id: "lesson-uuid-2",
    enrollment_id: "enrollment-uuid-1",
    watch_time_seconds: 450,  // Watched 7.5 out of 15 minutes (50%)
    completed: false,
    last_watched_at: "2025-10-20T10:30:00Z"
  }
]

// COURSE PROGRESS SUMMARY (from get_course_progress function)
{
  total_lessons: 24,
  completed_lessons: 1,
  in_progress_lessons: 1,
  total_duration_seconds: 14400,  // 4 hours
  watched_duration_seconds: 1170,  // 19.5 minutes
  completion_percentage: 4.17,
  last_watched_at: "2025-10-20T10:30:00Z"
}

9.4 Certificate Example

{
  id: "cert-uuid-1",
  user_id: "user-uuid-123",
  course_id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
  certificate_code: "CERT-1KL8M9N-AB12CD",
  issued_at: "2025-10-30T14:30:00Z",
  completion_percentage: 100.00
}

// Certificate verification URL:
// https://yourplatform.com/verify/CERT-1KL8M9N-AB12CD

---

10. Next Steps - Decision Points

⚠️ IMPORTANT: Answer These Before Implementation

1. Admin Access Control

Question: Who should have admin access initially?

Options:

• Just you (one super admin)
• Multiple instructors (role-based access)
• Team with different permissions

Decision: Three roles: super_admin, instructor, and viewer. Start with super_admin for initial user.

---

2. Course Enrollment Strategy

Question: How should students enroll in courses?

Options:

• Auto-enroll on signup (all free courses accessible)
• Manual enrollment per course (click "Enroll" button)
• Waitlist integration (use existing track_waitlist table)
• Hybrid (auto-enroll in featured courses, manual for others)

Decision: Manual enrollment via "Enroll" button when viewing video. Show enrollment prompt if user tries to watch without enrollment. Waitlist integration for coming-soon courses.

---

3. Lesson Progression

Question: Should lessons be sequential or free-form?

Options:

• Sequential (must complete lesson 1 before lesson 2)
• Free-form (can skip around)
• Hybrid (sections are sequential, lessons within section are free)

Decision: Hybrid - Lessons within sections are free-form (can skip around). No locking mechanism needed.

---

4. Completion Threshold

Question: What % watched counts as "completed"?

Options:

• 90% (recommended - allows skipping credits)
• 100% (must watch entire video)
• 80% (more lenient)

Decision: 90% watch time marks lesson as complete (default recommendation)

---

5. Certificates

Question: Certificate issuance strategy?

Options:

• Auto-issue on 100% completion
• Auto-issue + require final quiz/assessment
• Manual approval by instructor
• No certificates (Phase 6 feature)

Decision: Auto-issue certificate on 100% course completion

If yes to certificates:

• I have a certificate design template
• Create a default template for me

---

6. Priority Features

Question: Which phases should we implement first?

Must Have (implement first):

• Phase 1: Database Setup
• Phase 2: YouTube Integration
• Phase 3: Admin Panel Core
• Phase 4: Student Dashboard

Nice to Have (implement if time allows):

• Phase 5: Analytics & Polish
• Phase 6: Advanced Features (quiz, forums, etc.)

Decision: MVP first - Phases 1-4 only. Polish and advanced features later.

---

7. Existing Content Migration

Question: Do you have existing content in nova_tracks to migrate?

• Yes - I have ___ existing tracks to convert to video courses
• No - Starting fresh

If yes:

• Migrate existing tracks to new course structure
• Keep old structure, start new alongside

Decision: No migration needed. Starting with fresh video course structure.

---

8. Branding & Design

Question: Design preferences?

• Match existing site design
• Create new modern design
• Use component library (shadcn/ui, Material UI, etc.)

Preferred color scheme:

• Current site colors
• Dark mode primary
• Light mode primary
• Auto (system preference)

Decision: Match existing site design and color scheme for consistency.

---

11. Progress Tracker

Database Setup ✅

• Create migration file
• Add course_sections table
• Add course_lessons table
• Add lesson_progress table
• Add lesson_notes table
• Add admin_users table
• Add course_certificates table
• Add indexes
• Add RLS policies
• Add database functions (get_course_progress, get_course_analytics)
• Test with sample data
• Verify RLS works

YouTube Integration ✅

• Get YouTube Data API key
• Add to environment variables
• Create youtube.js service
• Implement extractVideoId()
• Implement getVideoMetadata()
• Implement formatDuration()
• Create VideoPlayer component
• Test video embedding
• Test progress tracking

Admin Panel ✅

• Create admin routes
• Create AdminLayout component
• Create useAdminAuth hook
• Dashboard page
• CourseList page
• CourseEditor page
• SectionManager component
• LessonManager component
• YouTubeVideoInput component
• Drag-and-drop reordering
• Test full course creation flow

Student Dashboard ✅

• Create dashboard routes
• Create DashboardLayout
• MyCourses page
• CourseCard component
• CourseDetail page
• LearningPage (video player)
• LessonSidebar component
• Progress tracking implementation
• LessonNotes component
• Progress page
• Test enrollment flow
• Test video watching flow
• Test progress saves correctly

Analytics & Polish ✅

• Admin analytics dashboard
• Enrollment charts
• Completion rate charts
• StudentTable component
• Individual student progress view
• Certificate generation
• Certificate design
• Email notifications
• Search/filter functionality
• Mobile responsive
• Performance optimization

Advanced Features (Optional)

• Quiz system
• Discussion forums
• Downloadable resources
• Course reviews/ratings
• Student messaging
• Live session integration
• Assignments
• Gamification

---

Implementation Notes

Last Updated: 2025-10-30 Current Phase: Phase 1 - Database Setup Next Action: Create database migration file

Notes:

• All decision points answered and documented
• Starting with MVP (Phases 1-4)
• Three admin roles: super_admin, instructor, viewer
• Manual enrollment with free-form lesson progression
• 90% watch time = lesson complete
• Auto-issue certificates on 100% completion
• Match existing site design

---

Resources & References

Documentation

• YouTube Data API
• YouTube IFrame Player API
• Supabase RLS
• React Router

Design Inspiration

• Coursera
• Udemy
• Skillshare
• Frontend Masters

Tools

• YouTube Thumbnail URL
• ISO 8601 Duration Parser

---

End of Plan