File size: 5,781 Bytes
5463318
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
-- 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]);