-- Enable UUID extension CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- ========================================== -- 1. Profiles Table -- ========================================== CREATE TABLE public.profiles ( id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY, full_name TEXT, work_email TEXT, institution_name TEXT, role TEXT, password TEXT, avatar_url TEXT, plan_type TEXT DEFAULT 'free', available_credits INTEGER DEFAULT 10, created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL ); -- Enable Row Level Security ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY; -- RLS Policies for Profiles CREATE POLICY "Users can view their own profile." ON public.profiles FOR SELECT USING (auth.uid() = id); CREATE POLICY "Users can update their own profile." ON public.profiles FOR UPDATE USING (auth.uid() = id); -- Trigger to automatically create a profile when a new user signs up in Auth CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS trigger AS $$ BEGIN INSERT INTO public.profiles (id, full_name, work_email, institution_name, role, avatar_url) VALUES ( new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'work_email', new.raw_user_meta_data->>'institution_name', new.raw_user_meta_data->>'role', new.raw_user_meta_data->>'avatar_url' ); RETURN new; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE OR REPLACE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user(); -- ========================================== -- 2. Materials Table -- ========================================== CREATE TABLE public.materials ( id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL, file_name TEXT NOT NULL, file_size INTEGER, storage_path TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL ); -- Enable Row Level Security ALTER TABLE public.materials ENABLE ROW LEVEL SECURITY; -- RLS Policies for Materials CREATE POLICY "Users can view their own materials." ON public.materials FOR SELECT USING (auth.uid() = user_id); CREATE POLICY "Users can insert their own materials." ON public.materials FOR INSERT WITH CHECK (auth.uid() = user_id); CREATE POLICY "Users can update their own materials." ON public.materials FOR UPDATE USING (auth.uid() = user_id); CREATE POLICY "Users can delete their own materials." ON public.materials FOR DELETE USING (auth.uid() = user_id); -- ========================================== -- 3. Exam Papers Table -- ========================================== CREATE TABLE public.exam_papers ( id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL, title TEXT NOT NULL, subject TEXT, total_marks INTEGER, total_questions INTEGER, paper_config JSONB, content JSONB, created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL ); -- Enable Row Level Security ALTER TABLE public.exam_papers ENABLE ROW LEVEL SECURITY; -- RLS Policies for Exam Papers CREATE POLICY "Users can view their own exam papers." ON public.exam_papers FOR SELECT USING (auth.uid() = user_id); CREATE POLICY "Users can insert their own exam papers." ON public.exam_papers FOR INSERT WITH CHECK (auth.uid() = user_id); CREATE POLICY "Users can update their own exam papers." ON public.exam_papers FOR UPDATE USING (auth.uid() = user_id); CREATE POLICY "Users can delete their own exam papers." ON public.exam_papers FOR DELETE USING (auth.uid() = user_id); -- ========================================== -- 4. Questions Table -- ========================================== CREATE TABLE public.questions ( id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, paper_id UUID REFERENCES public.exam_papers(id) ON DELETE CASCADE NOT NULL, question_type TEXT, question_text TEXT, options JSONB, correct_answer TEXT, marks INTEGER, explanation TEXT ); -- Enable Row Level Security ALTER TABLE public.questions ENABLE ROW LEVEL SECURITY; -- RLS Policies for Questions (Based on exam_papers ownership) CREATE POLICY "Users can view questions of their own exam papers." ON public.questions FOR SELECT USING (EXISTS ( SELECT 1 FROM public.exam_papers WHERE id = public.questions.paper_id AND user_id = auth.uid() )); CREATE POLICY "Users can insert questions to their own exam papers." ON public.questions FOR INSERT WITH CHECK (EXISTS ( SELECT 1 FROM public.exam_papers WHERE id = public.questions.paper_id AND user_id = auth.uid() )); CREATE POLICY "Users can update questions of their own exam papers." ON public.questions FOR UPDATE USING (EXISTS ( SELECT 1 FROM public.exam_papers WHERE id = public.questions.paper_id AND user_id = auth.uid() )); CREATE POLICY "Users can delete questions of their own exam papers." ON public.questions FOR DELETE USING (EXISTS ( SELECT 1 FROM public.exam_papers WHERE id = public.questions.paper_id AND user_id = auth.uid() )); -- ========================================== -- Optional: Storage Bucket Policies -- (Assuming you create a bucket named 'materials' in the Supabase Dashboard) -- ========================================== -- CREATE POLICY "Users can upload materials to their own folder." -- ON storage.objects FOR INSERT -- WITH CHECK (bucket_id = 'materials' AND auth.uid()::text = (storage.foldername(name))[1]); -- CREATE POLICY "Users can read their own materials." -- ON storage.objects FOR SELECT -- USING (bucket_id = 'materials' AND auth.uid()::text = (storage.foldername(name))[1]);