Progress Analytics

CREATE TABLE quiz_results (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id),
  quiz_id UUID REFERENCES quizzes(id),
  score INTEGER NOT NULL,
  total_questions INTEGER NOT NULL,
  percentage INTEGER NOT NULL,
  answers JSONB NOT NULL,
  submitted_at TIMESTAMPTZ DEFAULT NOW()
);

// Get your quiz history
GET /api/quiz/history

// Response includes:
{
  quizzes: [
    {
      id: "quiz-uuid",
      source_name: "Chapter 5: Calculus",
      created_at: "2026-03-01T10:00:00Z",
      results: [
        {
          score: 8,
          total_questions: 10,
          percentage: 80,
          submitted_at: "2026-03-01T10:15:00Z"
        }
      ]
    }
  ]
}

CREATE TABLE quiz_attempts (
  id UUID PRIMARY KEY,
  quiz_id UUID REFERENCES quizzes(id),
  user_id UUID REFERENCES auth.users(id),
  attempt_name TEXT NOT NULL,  -- Display name for guest users
  is_guest BOOLEAN DEFAULT false,
  score INTEGER NOT NULL,
  total_questions INTEGER NOT NULL,
  percentage INTEGER NOT NULL,
  answers JSONB NOT NULL,
  completed_at TIMESTAMPTZ DEFAULT NOW()
);

// Get all attempts for your shared quiz
GET /api/quiz/:quizId/attempts

// Response includes:
{
  quiz: { /* quiz details */ },
  stats: {
    total_attempts: 45,
    average_score: 7.2,
    highest_score: 10,
    lowest_score: 3,
    average_percentage: 72.5
  },
  attempts: [
    {
      attempt_name: "John D.",
      score: 8,
      percentage: 80,
      completed_at: "2026-03-03T14:30:00Z",
      is_guest: false
    },
    // ...
  ]
}

CREATE TABLE study_activity (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id),
  activity_date DATE NOT NULL,
  activity_type VARCHAR(50) NOT NULL,
  activity_count INTEGER DEFAULT 1,
  total_time_minutes INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, activity_date, activity_type)
);

GET /api/streaks/stats

// Returns comprehensive activity breakdown:
{
  total_activities: 150,
  total_study_time: 3420,  // minutes
  breakdown: {
    quiz: 45,
    timer: 30,
    flashcard: 25,
    doubt: 20,
    notes: 15,
    // ...
  },
  daily_average: 8.5,
  weekly_trend: [12, 15, 9, 14, 11, 13, 16]
}

CREATE TABLE flashcard_progress (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  deck_id UUID NOT NULL,
  card_id TEXT NOT NULL,
  mastery_level INTEGER DEFAULT 0,  -- 0-5
  ease_factor DECIMAL(4,2) DEFAULT 2.50,
  interval_days INTEGER DEFAULT 0,
  last_reviewed_at TIMESTAMPTZ,
  next_review_at TIMESTAMPTZ DEFAULT NOW(),
  review_count INTEGER DEFAULT 0,
  correct_count INTEGER DEFAULT 0,
  UNIQUE(user_id, deck_id, card_id)
);

CREATE TABLE flashcard_sessions (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  deck_id UUID NOT NULL,
  study_mode TEXT,  -- 'flip', 'mcq', 'type'
  cards_studied INTEGER DEFAULT 0,
  cards_correct INTEGER DEFAULT 0,
  duration_seconds INTEGER,
  session_data JSONB,
  completed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE practice_test_attempts (
  id UUID PRIMARY KEY,
  test_id UUID REFERENCES practice_tests(id),
  user_id UUID REFERENCES auth.users(id),
  answers JSONB NOT NULL,
  score INTEGER,
  max_score INTEGER,
  time_taken_seconds INTEGER,
  ai_feedback JSONB,  -- AI-generated performance insights
  completed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE daily_goal_progress (
  user_id UUID,
  goal_date DATE,
  minutes_done INTEGER DEFAULT 0,
  sessions_done INTEGER DEFAULT 0,
  tasks_done INTEGER DEFAULT 0,
  updated_at TIMESTAMPTZ,
  PRIMARY KEY (user_id, goal_date)
);

// Get today's progress
GET /api/goals/daily/today

{
  goal_date: "2026-03-03",
  targets: {
    minutes: 60,
    sessions: 2,
    tasks: 3
  },
  progress: {
    minutes_done: 45,
    sessions_done: 1,
    tasks_done: 2
  },
  completion: {
    minutes: 75%,
    sessions: 50%,
    tasks: 67%,
    overall: 64%
  }
}

// Get habit check-ins for date range
GET /api/goals/habits/checkins?from=2026-03-01&to=2026-03-31

{
  checkins: [
    {
      habit_id: "uuid",
      checkin_date: "2026-03-03",
      done: true
    },
    // ...
  ],
  stats: {
    total_days: 31,
    completed_days: 24,
    completion_rate: 77.4,
    current_streak: 7,
    longest_streak: 12
  }
}

-- Row Level Security ensures users only see their own data
CREATE POLICY "Users can view own quiz results"
  ON quiz_results FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can view own study activity"
  ON study_activity FOR SELECT
  USING (auth.uid() = user_id);