> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pixlnext.tech/llms.txt
> Use this file to discover all available pages before exploring further.

# Database migrations

# Database Migrations

This document details all database migrations and schema changes in the STO Education Platform.

## Migration System Overview

The platform uses Supabase for database management with SQL migration files stored in the `supabase/migrations/` directory. Migrations are applied automatically during deployment and can be run manually for development.

## Migration Files Structure

```
supabase/migrations/
├── 20240101000000_initial_schema.sql
├── 20240102000000_add_user_profiles.sql
├── 20240103000000_add_courses_system.sql
├── 20240104000000_add_sessions_system.sql
├── 20240105000000_add_payment_system.sql
├── 20240106000000_add_quiz_system.sql
├── 20240107000000_add_teacher_mark_schemes.sql
└── 20240108000000_add_analytics_tables.sql
```

## Core Schema Migrations

### Initial Schema Setup

**File**: `20240101000000_initial_schema.sql`

```sql theme={null}
-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create custom types
CREATE TYPE user_role AS ENUM ('student', 'teacher', 'parent', 'trainee', 'admin');
CREATE TYPE session_status AS ENUM ('scheduled', 'ongoing', 'completed', 'cancelled');
CREATE TYPE payment_status AS ENUM ('pending', 'processing', 'completed', 'failed', 'refunded');
CREATE TYPE course_status AS ENUM ('draft', 'published', 'archived');

-- Create profiles table
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  username TEXT UNIQUE,
  full_name TEXT,
  email TEXT,
  role user_role DEFAULT 'student',
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create education_systems table
CREATE TABLE education_systems (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  country TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create subjects table
CREATE TABLE subjects (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  education_system_id UUID REFERENCES education_systems(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE education_systems ENABLE ROW LEVEL SECURITY;
ALTER TABLE subjects ENABLE ROW LEVEL SECURITY;
```

### User Profiles System

**File**: `20240102000000_add_user_profiles.sql`

```sql theme={null}
-- Add additional profile fields
ALTER TABLE profiles ADD COLUMN phone_number TEXT;
ALTER TABLE profiles ADD COLUMN date_of_birth DATE;
ALTER TABLE profiles ADD COLUMN gender TEXT;
ALTER TABLE profiles ADD COLUMN address TEXT;
ALTER TABLE profiles ADD COLUMN bio TEXT;
ALTER TABLE profiles ADD COLUMN is_active BOOLEAN DEFAULT true;

-- Create teacher_subjects junction table
CREATE TABLE teacher_subjects (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  subject_id UUID REFERENCES subjects(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(teacher_id, subject_id)
);

-- Create student_parents junction table
CREATE TABLE student_parents (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  parent_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  relationship TEXT DEFAULT 'parent',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(student_id, parent_id)
);

-- Add indexes for performance
CREATE INDEX idx_profiles_role ON profiles(role);
CREATE INDEX idx_profiles_email ON profiles(email);
CREATE INDEX idx_teacher_subjects_teacher ON teacher_subjects(teacher_id);
CREATE INDEX idx_teacher_subjects_subject ON teacher_subjects(subject_id);
```

### Courses System

**File**: `20240103000000_add_courses_system.sql`

```sql theme={null}
-- Create courses table
CREATE TABLE courses (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  slug TEXT UNIQUE NOT NULL,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  subject_id UUID REFERENCES subjects(id),
  education_system_id UUID REFERENCES education_systems(id),
  price DECIMAL(10,2) DEFAULT 0,
  currency TEXT DEFAULT 'EGP',
  status course_status DEFAULT 'draft',
  thumbnail_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create course_enrollments table
CREATE TABLE course_enrollments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE,
  progress_percentage INTEGER DEFAULT 0,
  UNIQUE(course_id, student_id)
);

-- Create course_modules table
CREATE TABLE course_modules (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  order_index INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create course_lessons table
CREATE TABLE course_lessons (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  module_id UUID REFERENCES course_modules(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  video_url TEXT,
  duration_minutes INTEGER,
  order_index INTEGER NOT NULL,
  is_free BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_courses_teacher ON courses(teacher_id);
CREATE INDEX idx_courses_subject ON courses(subject_id);
CREATE INDEX idx_courses_status ON courses(status);
CREATE INDEX idx_enrollments_student ON course_enrollments(student_id);
CREATE INDEX idx_enrollments_course ON course_enrollments(course_id);
```

### Sessions System

**File**: `20240104000000_add_sessions_system.sql`

```sql theme={null}
-- Create sessions table
CREATE TABLE sessions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
  duration_minutes INTEGER DEFAULT 60,
  max_participants INTEGER,
  status session_status DEFAULT 'scheduled',
  room_id TEXT,
  recording_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create session_participants table
CREATE TABLE session_participants (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  session_id UUID REFERENCES sessions(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  joined_at TIMESTAMP WITH TIME ZONE,
  left_at TIMESTAMP WITH TIME ZONE,
  attendance_status TEXT DEFAULT 'pending',
  UNIQUE(session_id, student_id)
);

-- Create session_attendance table
CREATE TABLE session_attendance (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  session_id UUID REFERENCES sessions(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  attended BOOLEAN DEFAULT false,
  attendance_time TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(session_id, student_id)
);

-- Add indexes
CREATE INDEX idx_sessions_teacher ON sessions(teacher_id);
CREATE INDEX idx_sessions_course ON sessions(course_id);
CREATE INDEX idx_sessions_scheduled ON sessions(scheduled_at);
CREATE INDEX idx_participants_session ON session_participants(session_id);
CREATE INDEX idx_attendance_student ON session_attendance(student_id);
```

### Payment System

**File**: `20240105000000_add_payment_system.sql`

```sql theme={null}
-- Create orders table
CREATE TABLE orders (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  order_type TEXT NOT NULL, -- 'course', 'training', 'mark_scheme'
  reference_id UUID, -- ID of the item being purchased
  amount DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'EGP',
  status payment_status DEFAULT 'pending',
  payment_method TEXT,
  payment_reference TEXT,
  paymob_order_id TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create payments table
CREATE TABLE payments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  amount DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'EGP',
  status payment_status DEFAULT 'pending',
  payment_method TEXT,
  transaction_id TEXT,
  gateway_response JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create training_enrollments table
CREATE TABLE training_enrollments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  trainee_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE,
  progress_percentage INTEGER DEFAULT 0,
  UNIQUE(course_id, trainee_id)
);

-- Add indexes
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_type ON orders(order_type);
CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_training_enrollments_trainee ON training_enrollments(trainee_id);
```

### Quiz System

**File**: `20240106000000_add_quiz_system.sql`

```sql theme={null}
-- Create quizzes table
CREATE TABLE quizzes (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  time_limit_minutes INTEGER,
  max_attempts INTEGER DEFAULT 3,
  passing_score INTEGER DEFAULT 70,
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create quiz_questions table
CREATE TABLE quiz_questions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  quiz_id UUID REFERENCES quizzes(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  question_type TEXT NOT NULL, -- 'multiple_choice', 'true_false', 'essay'
  options JSONB, -- For multiple choice questions
  correct_answer TEXT,
  points INTEGER DEFAULT 1,
  order_index INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create quiz_attempts table
CREATE TABLE quiz_attempts (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  quiz_id UUID REFERENCES quizzes(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE,
  score INTEGER,
  total_points INTEGER,
  is_passed BOOLEAN,
  attempt_number INTEGER DEFAULT 1
);

-- Create quiz_responses table
CREATE TABLE quiz_responses (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  attempt_id UUID REFERENCES quiz_attempts(id) ON DELETE CASCADE,
  question_id UUID REFERENCES quiz_questions(id) ON DELETE CASCADE,
  answer TEXT,
  is_correct BOOLEAN,
  points_earned INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_quizzes_course ON quizzes(course_id);
CREATE INDEX idx_quizzes_teacher ON quizzes(teacher_id);
CREATE INDEX idx_questions_quiz ON quiz_questions(quiz_id);
CREATE INDEX idx_attempts_quiz ON quiz_attempts(quiz_id);
CREATE INDEX idx_attempts_student ON quiz_attempts(student_id);
CREATE INDEX idx_responses_attempt ON quiz_responses(attempt_id);
```

### Teacher Mark Schemes

**File**: `20240107000000_add_teacher_mark_schemes.sql`

```sql theme={null}
-- Create teacher_mark_schemes table
CREATE TABLE teacher_mark_schemes (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  subject_id UUID REFERENCES subjects(id) ON DELETE CASCADE,
  original_filename TEXT NOT NULL,
  file_url TEXT NOT NULL,
  exam_category TEXT NOT NULL, -- 'igcse', 'as_level', 'a_level', etc.
  paper_year TEXT NOT NULL,
  paper_season TEXT NOT NULL, -- 'summer', 'winter', 'march'
  paper_category TEXT NOT NULL, -- 'paper_1', 'paper_2', etc.
  teacher_explanation TEXT,
  price DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'EGP',
  is_published BOOLEAN DEFAULT false,
  download_count INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create teacher_mark_scheme_purchases table
CREATE TABLE teacher_mark_scheme_purchases (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  mark_scheme_id UUID REFERENCES teacher_mark_schemes(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  purchased_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  download_count INTEGER DEFAULT 0,
  last_downloaded_at TIMESTAMP WITH TIME ZONE
);

-- Create assignments table
CREATE TABLE assignments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  due_date TIMESTAMP WITH TIME ZONE,
  max_points INTEGER DEFAULT 100,
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create assignment_submissions table
CREATE TABLE assignment_submissions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  assignment_id UUID REFERENCES assignments(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  submission_text TEXT,
  file_url TEXT,
  submitted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  graded_at TIMESTAMP WITH TIME ZONE,
  grade INTEGER,
  feedback TEXT,
  is_late BOOLEAN DEFAULT false
);

-- Add indexes
CREATE INDEX idx_mark_schemes_teacher ON teacher_mark_schemes(teacher_id);
CREATE INDEX idx_mark_schemes_subject ON teacher_mark_schemes(subject_id);
CREATE INDEX idx_mark_schemes_category ON teacher_mark_schemes(exam_category);
CREATE INDEX idx_purchases_student ON teacher_mark_scheme_purchases(student_id);
CREATE INDEX idx_assignments_course ON assignments(course_id);
CREATE INDEX idx_submissions_student ON assignment_submissions(student_id);
```

### Analytics and Monitoring

**File**: `20240108000000_add_analytics_tables.sql`

```sql theme={null}
-- Create user_analytics table
CREATE TABLE user_analytics (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  event_data JSONB,
  session_id TEXT,
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create page_views table
CREATE TABLE page_views (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE SET NULL,
  page_url TEXT NOT NULL,
  page_title TEXT,
  referrer TEXT,
  session_id TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create mochi_scores table
CREATE TABLE mochi_scores (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  overall_score INTEGER CHECK (overall_score >= 0 AND overall_score <= 100),
  level INTEGER CHECK (level >= 1 AND level <= 5),
  mood TEXT CHECK (mood IN ('happy', 'neutral', 'sad')),
  calculation_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create blog_posts table
CREATE TABLE blog_posts (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  content TEXT NOT NULL,
  excerpt TEXT,
  author_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  featured_image_url TEXT,
  is_published BOOLEAN DEFAULT false,
  published_at TIMESTAMP WITH TIME ZONE,
  view_count INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create page_content table
CREATE TABLE page_content (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  page_key TEXT UNIQUE NOT NULL,
  title TEXT,
  content TEXT NOT NULL,
  metadata JSONB,
  updated_by UUID REFERENCES profiles(id) ON DELETE SET NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_analytics_user ON user_analytics(user_id);
CREATE INDEX idx_analytics_event ON user_analytics(event_type);
CREATE INDEX idx_page_views_user ON page_views(user_id);
CREATE INDEX idx_page_views_url ON page_views(page_url);
CREATE INDEX idx_mochi_scores_student ON mochi_scores(student_id);
CREATE INDEX idx_mochi_scores_course ON mochi_scores(course_id);
CREATE INDEX idx_blog_posts_author ON blog_posts(author_id);
CREATE INDEX idx_blog_posts_published ON blog_posts(is_published);
CREATE INDEX idx_page_content_key ON page_content(page_key);
```

## Row Level Security (RLS) Policies

### Profiles Table Policies

```sql theme={null}
-- Users can view their own profile
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

-- Users can update their own profile
CREATE POLICY "Users can update own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id);

-- Admins can view all profiles
CREATE POLICY "Admins can view all profiles" ON profiles
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'admin'
    )
  );
```

### Courses Table Policies

```sql theme={null}
-- Anyone can view published courses
CREATE POLICY "Anyone can view published courses" ON courses
  FOR SELECT USING (status = 'published');

-- Teachers can manage their own courses
CREATE POLICY "Teachers can manage own courses" ON courses
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'teacher'
    ) AND teacher_id = auth.uid()
  );
```

### Sessions Table Policies

```sql theme={null}
-- Students can view sessions for enrolled courses
CREATE POLICY "Students can view enrolled course sessions" ON sessions
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM course_enrollments 
      WHERE course_id = sessions.course_id 
      AND student_id = auth.uid()
    )
  );

-- Teachers can manage their own sessions
CREATE POLICY "Teachers can manage own sessions" ON sessions
  FOR ALL USING (teacher_id = auth.uid());
```

## Functions and Triggers

### Updated At Trigger

```sql theme={null}
-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply trigger to relevant tables
CREATE TRIGGER update_profiles_updated_at 
  BEFORE UPDATE ON profiles 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_courses_updated_at 
  BEFORE UPDATE ON courses 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_sessions_updated_at 
  BEFORE UPDATE ON sessions 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
```

### Mochi Score Calculation Function

```sql theme={null}
-- Function to calculate Mochi score
CREATE OR REPLACE FUNCTION calculate_mochi_score(
  p_student_id UUID,
  p_course_id UUID
) RETURNS TABLE(
  overall_score INTEGER,
  level INTEGER,
  mood TEXT
) AS $$
DECLARE
  v_assignment_score NUMERIC;
  v_attendance_score NUMERIC;
  v_quiz_score NUMERIC;
  v_final_score INTEGER;
  v_level INTEGER;
  v_mood TEXT;
BEGIN
  -- Calculate assignment score (40% weight)
  SELECT COALESCE(AVG(grade), 0) INTO v_assignment_score
  FROM assignment_submissions 
  WHERE student_id = p_student_id 
  AND assignment_id IN (
    SELECT id FROM assignments WHERE course_id = p_course_id
  );

  -- Calculate attendance score (30% weight)
  SELECT COALESCE(
    (COUNT(*) FILTER (WHERE attended = true)::NUMERIC / 
     NULLIF(COUNT(*), 0)) * 100, 0
  ) INTO v_attendance_score
  FROM session_attendance sa
  JOIN sessions s ON sa.session_id = s.id
  WHERE sa.student_id = p_student_id 
  AND s.course_id = p_course_id;

  -- Calculate quiz score (30% weight)
  SELECT COALESCE(AVG(score), 0) INTO v_quiz_score
  FROM quiz_attempts qa
  JOIN quizzes q ON qa.quiz_id = q.id
  WHERE qa.student_id = p_student_id 
  AND q.course_id = p_course_id;

  -- Calculate final score
  v_final_score := ROUND(
    (v_assignment_score * 0.4) + 
    (v_attendance_score * 0.3) + 
    (v_quiz_score * 0.3)
  );

  -- Determine level (1-5)
  v_level := CASE
    WHEN v_final_score >= 90 THEN 5
    WHEN v_final_score >= 75 THEN 4
    WHEN v_final_score >= 60 THEN 3
    WHEN v_final_score >= 40 THEN 2
    ELSE 1
  END;

  -- Determine mood
  v_mood := CASE
    WHEN v_final_score >= 80 THEN 'happy'
    WHEN v_final_score >= 50 THEN 'neutral'
    ELSE 'sad'
  END;

  RETURN QUERY SELECT v_final_score, v_level, v_mood;
END;
$$ LANGUAGE plpgsql;
```

## Migration Management

### Running Migrations

```bash theme={null}
# Run all pending migrations
npm run run-migrations

# Or using Supabase CLI
supabase db reset
supabase db push
```

### Migration Best Practices

1. **Atomic Changes**: Each migration should be atomic and reversible
2. **Naming Convention**: Use timestamp prefix for ordering
3. **Backward Compatibility**: Ensure migrations don't break existing data
4. **Testing**: Test migrations on staging environment first
5. **Rollback Plan**: Always have a rollback strategy

### Migration Scripts

```javascript theme={null}
// scripts/db-migration-runner.js
const { createClient } = require('@supabase/supabase-js')

async function runMigrations() {
  const supabase = createClient(
    process.env.VITE_SUPABASE_URL,
    process.env.VITE_SUPABASE_SERVICE_ROLE_KEY
  )
  
  const migrationsDir = path.join(__dirname, '../supabase/migrations')
  const migrationFiles = fs.readdirSync(migrationsDir)
    .filter(file => file.endsWith('.sql'))
    .sort()
  
  for (const file of migrationFiles) {
    const migrationPath = path.join(migrationsDir, file)
    const sql = fs.readFileSync(migrationPath, 'utf8')
    
    const { error } = await supabase.rpc('exec_sql', { sql })
    
    if (error) {
      console.error(`Migration failed: ${file}`, error)
      process.exit(1)
    }
    
    console.log(`Migration completed: ${file}`)
  }
}
```

## Related Documentation

* [Database Schema](/docs/backend/database-schema) - Complete database structure
* [Supabase Functions](/docs/backend/supabase-functions) - Edge functions and server logic
* [Utility Scripts](/docs/scripts/utility-scripts) - Migration and backup scripts
* [Backend Development](/docs/backend) - Backend architecture and services