setup-database.sql

-- StudyHQ Database Setup
-- Run this in Supabase SQL Editor: https://supabase.com/dashboard/project/rfirijxfxpyvmpphdhyr/sql

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================
-- PROFILES TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name TEXT,
  level TEXT, -- 'primary-1' to 'university-4'
  exam_board TEXT, -- 'jamb', 'waec', 'neco', etc.
  subjects TEXT[], -- array of subject codes
  school_id UUID,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- TEST SESSIONS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS test_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  subject TEXT NOT NULL,
  exam_board TEXT NOT NULL,
  difficulty TEXT, -- 'easy', 'medium', 'hard'
  question_count INTEGER NOT NULL,
  correct_count INTEGER NOT NULL,
  score_percentage DECIMAL(5,2),
  time_taken_seconds INTEGER,
  completed_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- TEST ANSWERS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS test_answers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  session_id UUID REFERENCES test_sessions(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  options JSONB, -- array of options
  user_answer TEXT,
  correct_answer TEXT NOT NULL,
  is_correct BOOLEAN,
  topic TEXT,
  difficulty TEXT,
  explanation TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- STUDY ROOMS TABLE (Group Study)
-- ============================================
CREATE TABLE IF NOT EXISTS study_rooms (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  room_code TEXT UNIQUE NOT NULL,
  creator_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  subject TEXT NOT NULL,
  question_count INTEGER NOT NULL,
  timer_minutes INTEGER NOT NULL,
  difficulty TEXT NOT NULL,
  status TEXT DEFAULT 'waiting', -- 'waiting', 'active', 'completed'
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- ROOM MEMBERS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS room_members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  room_id UUID REFERENCES study_rooms(id) ON DELETE CASCADE,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  joined_at TIMESTAMP DEFAULT NOW(),
  score INTEGER,
  rank INTEGER,
  UNIQUE(room_id, user_id)
);

-- ============================================
-- ENABLE ROW LEVEL SECURITY
-- ============================================
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE test_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE test_answers ENABLE ROW LEVEL SECURITY;
ALTER TABLE study_rooms ENABLE ROW LEVEL SECURITY;
ALTER TABLE room_members ENABLE ROW LEVEL SECURITY;

-- ============================================
-- PROFILES POLICIES
-- ============================================
DROP POLICY IF EXISTS "Users can view own profile" ON profiles;
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

DROP POLICY IF EXISTS "Users can insert own profile" ON profiles;
CREATE POLICY "Users can insert own profile" ON profiles
  FOR INSERT WITH CHECK (auth.uid() = id);

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

-- ============================================
-- TEST SESSIONS POLICIES
-- ============================================
DROP POLICY IF EXISTS "Users can view own sessions" ON test_sessions;
CREATE POLICY "Users can view own sessions" ON test_sessions
  FOR SELECT USING (auth.uid() = user_id);

DROP POLICY IF EXISTS "Users can insert own sessions" ON test_sessions;
CREATE POLICY "Users can insert own sessions" ON test_sessions
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- ============================================
-- TEST ANSWERS POLICIES
-- ============================================
DROP POLICY IF EXISTS "Users can view own answers" ON test_answers;
CREATE POLICY "Users can view own answers" ON test_answers
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM test_sessions 
      WHERE test_sessions.id = test_answers.session_id 
      AND test_sessions.user_id = auth.uid()
    )
  );

DROP POLICY IF EXISTS "Users can insert own answers" ON test_answers;
CREATE POLICY "Users can insert own answers" ON test_answers
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM test_sessions 
      WHERE test_sessions.id = test_answers.session_id 
      AND test_sessions.user_id = auth.uid()
    )
  );

-- ============================================
-- STUDY ROOMS POLICIES
-- ============================================
DROP POLICY IF EXISTS "Anyone can view active rooms" ON study_rooms;
CREATE POLICY "Anyone can view active rooms" ON study_rooms
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "Users can create rooms" ON study_rooms;
CREATE POLICY "Users can create rooms" ON study_rooms
  FOR INSERT WITH CHECK (auth.uid() = creator_id);

DROP POLICY IF EXISTS "Creators can update own rooms" ON study_rooms;
CREATE POLICY "Creators can update own rooms" ON study_rooms
  FOR UPDATE USING (auth.uid() = creator_id);

-- ============================================
-- ROOM MEMBERS POLICIES
-- ============================================
DROP POLICY IF EXISTS "Anyone can view room members" ON room_members;
CREATE POLICY "Anyone can view room members" ON room_members
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "Users can join rooms" ON room_members;
CREATE POLICY "Users can join rooms" ON room_members
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- ============================================
-- FUNCTIONS
-- ============================================

-- Function to create profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, created_at, updated_at)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'full_name', ''),
    NOW(),
    NOW()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger to auto-create profile
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================
CREATE INDEX IF NOT EXISTS idx_test_sessions_user_id ON test_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_test_sessions_created_at ON test_sessions(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_test_answers_session_id ON test_answers(session_id);
CREATE INDEX IF NOT EXISTS idx_study_rooms_status ON study_rooms(status);
CREATE INDEX IF NOT EXISTS idx_study_rooms_room_code ON study_rooms(room_code);
CREATE INDEX IF NOT EXISTS idx_room_members_room_id ON room_members(room_id);
CREATE INDEX IF NOT EXISTS idx_room_members_user_id ON room_members(user_id);

-- ============================================
-- SUCCESS MESSAGE
-- ============================================
SELECT 'StudyHQ database setup completed successfully! ✅' AS status;