Data Model

-- Users table (managed by Supabase Auth)
auth.users (
  id UUID PRIMARY KEY,
  email TEXT UNIQUE,
  created_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ
)

-- User profiles with role-based access
public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  first_name TEXT,
  last_name TEXT,
  role TEXT CHECK (role IN ('student', 'trainer', 'university', 'admin')),
  avatar_url TEXT,
  bio TEXT,
  linkedin_url TEXT,
  github_url TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
)

-- Student profiles (extends profiles)
public.student_profiles (
  id UUID PRIMARY KEY REFERENCES profiles(id),
  university_id UUID,
  graduation_year INTEGER,
  current_gpa DECIMAL(3,2),
  skills TEXT[],
  career_goals TEXT,
  enrollment_status TEXT CHECK (status IN ('active', 'inactive', 'graduated'))
)

-- Trainer profiles (extends profiles)
public.trainer_profiles (
  id UUID PRIMARY KEY REFERENCES profiles(id),
  expertise TEXT[],
  experience_years INTEGER,
  certifications TEXT[],
  rating DECIMAL(3,2),
  total_students INTEGER DEFAULT 0
)

-- University profiles (extends profiles)
public.university_profiles (
  id UUID PRIMARY KEY REFERENCES profiles(id),
  institution_name TEXT,
  accreditation_status TEXT,
  location TEXT,
  website_url TEXT,
  total_students INTEGER DEFAULT 0
)

-- Course catalog
public.courses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  description TEXT,
  thumbnail_url TEXT,
  video_id TEXT, -- YouTube/Vimeo ID
  category TEXT,
  technologies TEXT[],
  difficulty TEXT CHECK (difficulty IN ('beginner', 'intermediate', 'advanced')),
  duration_hours INTEGER,
  price DECIMAL(10,2),
  original_price DECIMAL(10,2),
  featured BOOLEAN DEFAULT false,
  published BOOLEAN DEFAULT false,
  created_by UUID REFERENCES trainer_profiles(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
)

-- Course modules/sections
public.course_modules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  order_index INTEGER NOT NULL,
  duration_minutes INTEGER,
  created_at TIMESTAMPTZ DEFAULT now()
)

-- Course content (videos, documents, etc.)
public.course_content (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  module_id UUID REFERENCES course_modules(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content_type TEXT CHECK (type IN ('video', 'document', 'quiz', 'assignment')),
  content_url TEXT,
  content_data JSONB, -- Additional metadata
  order_index INTEGER NOT NULL,
  is_preview BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
)

-- Student course enrollments
public.enrollments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id UUID REFERENCES student_profiles(id),
  course_id UUID REFERENCES courses(id),
  enrollment_date TIMESTAMPTZ DEFAULT now(),
  completion_date TIMESTAMPTZ,
  progress_percentage DECIMAL(5,2) DEFAULT 0,
  certificate_issued BOOLEAN DEFAULT false,
  certificate_url TEXT,
  payment_status TEXT CHECK (status IN ('pending', 'completed', 'refunded')),
  amount_paid DECIMAL(10,2),
  UNIQUE(student_id, course_id)
)

-- Student progress through course content
public.student_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  enrollment_id UUID REFERENCES enrollments(id) ON DELETE CASCADE,
  content_id UUID REFERENCES course_content(id),
  completed BOOLEAN DEFAULT false,
  completed_at TIMESTAMPTZ,
  time_spent_minutes INTEGER DEFAULT 0,
  score DECIMAL(5,2), -- For quizzes/assignments
  attempts INTEGER DEFAULT 1,
  UNIQUE(enrollment_id, content_id)
)

-- Student notes and bookmarks
public.student_notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id UUID REFERENCES student_profiles(id),
  course_id UUID REFERENCES courses(id),
  content_id UUID REFERENCES course_content(id),
  note_text TEXT,
  note_type TEXT CHECK (type IN ('highlight', 'note', 'question')),
  created_at TIMESTAMPTZ DEFAULT now()
)

-- Strength finder assessment questions
public.strength_finder_questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  question_text TEXT NOT NULL,
  category TEXT, -- 'executing', 'influencing', 'relationship_building', 'strategic_thinking'
  question_type TEXT CHECK (type IN ('multiple_choice', 'rating', 'open_ended')),
  options JSONB, -- For multiple choice questions
  order_index INTEGER NOT NULL,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now()
)

-- Student strength finder responses
public.strength_finder_responses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id UUID REFERENCES student_profiles(id),
  question_id UUID REFERENCES strength_finder_questions(id),
  response_value JSONB, -- Answer data
  response_score INTEGER, -- Numerical score
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(student_id, question_id)
)

-- Strength finder results
public.strength_finder_results (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id UUID REFERENCES student_profiles(id),
  top_strengths JSONB, -- Array of top 5 strengths
  detailed_scores JSONB, -- Full category scores
  career_recommendations JSONB, -- AI-generated recommendations
  completed_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(student_id)
)

-- Course-specific assessments/quizzes
public.course_assessments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  module_id UUID REFERENCES course_modules(id),
  title TEXT NOT NULL,
  description TEXT,
  assessment_type TEXT CHECK (type IN ('quiz', 'assignment', 'project')),
  questions JSONB, -- Assessment questions
  passing_score DECIMAL(5,2), -- Minimum passing percentage
  time_limit_minutes INTEGER,
  max_attempts INTEGER DEFAULT 1,
  is_required BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now()
)

-- Student assessment submissions
public.assessment_submissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  assessment_id UUID REFERENCES course_assessments(id),
  student_id UUID REFERENCES student_profiles(id),
  enrollment_id UUID REFERENCES enrollments(id),
  submission_data JSONB, -- Answers/responses
  score DECIMAL(5,2),
  max_score DECIMAL(5,2),
  percentage DECIMAL(5,2),
  passed BOOLEAN,
  attempt_number INTEGER DEFAULT 1,
  submitted_at TIMESTAMPTZ DEFAULT now(),
  graded_at TIMESTAMPTZ,
  graded_by UUID REFERENCES trainer_profiles(id),
  feedback TEXT
)

-- Students can only see their own profile and enrollments
CREATE POLICY "Students access own data" ON student_profiles
  FOR ALL USING (auth.uid() = id);

-- Trainers can see their created courses and enrolled students
CREATE POLICY "Trainers access course data" ON courses
  FOR SELECT USING (created_by = auth.uid());

-- Students can view courses but only enrolled students see full content
CREATE POLICY "Public course access" ON courses
  FOR SELECT USING (published = true);

CREATE POLICY "Enrolled students see all content" ON course_content
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM enrollments e
      WHERE e.student_id = auth.uid() AND e.course_id = course_content.course_id
    )
  );

-- Universities can manage their students and programs
CREATE POLICY "Universities manage students" ON student_profiles
  FOR ALL USING (university_id IN (
    SELECT id FROM university_profiles WHERE id = auth.uid()
  ));

-- Standard audit fields
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
created_by UUID REFERENCES profiles(id),
updated_by UUID REFERENCES profiles(id)

-- Assessment questions structure
questions JSONB -- [
--   {
--     "id": "q1",
--     "text": "Question text",
--     "type": "multiple_choice",
--     "options": ["Option 1", "Option 2"],
--     "weight": 1.0
--   }
-- ]

-- User and profile indexes
CREATE INDEX idx_profiles_role ON profiles(role);
CREATE INDEX idx_student_profiles_university ON student_profiles(university_id);
CREATE INDEX idx_trainer_profiles_rating ON trainer_profiles(rating);

-- Course and content indexes
CREATE INDEX idx_courses_published ON courses(published);
CREATE INDEX idx_courses_category ON courses(category);
CREATE INDEX idx_courses_created_by ON courses(created_by);
CREATE INDEX idx_course_content_module_id ON course_content(module_id);
CREATE INDEX idx_course_content_type ON course_content(content_type);

-- Enrollment and progress indexes
CREATE INDEX idx_enrollments_student_id ON enrollments(student_id);
CREATE INDEX idx_enrollments_course_id ON enrollments(course_id);
CREATE INDEX idx_enrollments_status ON enrollments(payment_status);
CREATE INDEX idx_student_progress_enrollment ON student_progress(enrollment_id);
CREATE INDEX idx_student_progress_completed ON student_progress(completed);

-- Assessment indexes
CREATE INDEX idx_assessment_submissions_student ON assessment_submissions(student_id);
CREATE INDEX idx_assessment_submissions_assessment ON assessment_submissions(assessment_id);
CREATE INDEX idx_strength_finder_responses_student ON strength_finder_responses(student_id);
CREATE INDEX idx_strength_finder_results_student ON strength_finder_results(student_id);

-- Search and filtering indexes
CREATE INDEX idx_courses_search ON courses USING gin(to_tsvector('english', title || ' ' || description));
CREATE INDEX idx_student_profiles_search ON student_profiles USING gin(to_tsvector('english', first_name || ' ' || last_name));