Changes between Version 5 and Version 6 of DatabaseProgramming-AdvDb

Timestamp:

06/07/26 16:24:09 (9 days ago)

Author:

231175

Comment:

--

DatabaseProgramming-AdvDb

@@ -6 +6 @@
 
 {{{
-create or replace function write_review(
-    user_id bigint,
-    course_version_id bigint,
-    new_rating int,
-    new_comment text
+create or replace function complete_quiz_attempt(
+    p_enrollment_id int,
+    p_quiz_id int,
+    p_answers jsonb -- [{question_id, answer_option_id}]
 )
-    returns review
-as
+    returns bigint
+    language plpgsql as
 $$
-    declare
-        r review;
-        e_id bigint;
-    begin
+declare
+    v_attempt_id    bigint;
+    v_attempt_num   int;
+    v_total_points  int;
+    v_earned_points int;
+    v_score         int;
+    v_passed        boolean;
+    v_passing_score numeric;
+begin
+    -- check answer count matches question count
+    if (select count(*) from jsonb_array_elements(p_answers)) !=
+       (select count(*) from quiz_question where quiz_id = p_quiz_id)
+    then
+        raise exception 'Answer count does not match question count for quiz %', p_quiz_id;
+    end if;
 
-        select e.id into e_id
-        from enrollment e
-        where e.user_id = write_review.user_id
-          and e.course_version_id = write_review.course_version_id
-        limit 1;
+    -- check all questions belong to the quiz
+    if exists (select 1
+               from jsonb_array_elements(p_answers) a
+               where not exists (select 1
+                                 from quiz_question
+                                 where id = (a ->> 'question_id')::bigint
+                                   and quiz_id = p_quiz_id)) then
+        raise exception 'One or more questions do not belong to quiz %', p_quiz_id;
+    end if;
 
-        -- Validate enrollment exists
-        if e_id is null then
-            raise exception 'No enrollment found for user % on course version %',
-                user_id, course_version_id
-                using errcode = 'NO_DATA_FOUND';
-        end if;
+    -- check all answer options belong to their respective questions
+    if exists (select 1
+               from jsonb_array_elements(p_answers) a
+               where not exists (select 1
+                                 from quiz_answer_option
+                                 where id = (a ->> 'answer_option_id')::bigint
+                                   and quiz_question_id = (a ->> 'question_id')::bigint)) then
+        raise exception 'One or more answer options do not belong to their question';
+    end if;
 
-        insert into review (rating, comment, date, enrollment_id)
-        values (new_rating, new_comment, now(), e_id)
-        returning * into r;
+    select coalesce(max(attempt_number), 0) + 1
+    into v_attempt_num
+    from quiz_attempt
+    where enrollment_id = p_enrollment_id
+      and quiz_id = p_quiz_id;
 
-        return r;
-    end;
-$$ language plpgsql;
+    select coalesce(sum(points), 0)
+    into v_total_points
+    from quiz_question
+    where quiz_id = p_quiz_id;
+
+    select coalesce(sum(qq.points), 0)
+    into v_earned_points
+    from jsonb_array_elements(p_answers) a
+             join quiz_question qq on qq.id = (a ->> 'question_id')::bigint
+             join quiz_answer_option qao on qao.id = (a ->> 'answer_option_id')::bigint
+    where qao.correct = true;
+
+    select passing_score
+    into v_passing_score
+    from quiz
+    where id = p_quiz_id;
+
+    v_score := case
+                   when v_total_points > 0
+                       then ((v_earned_points::numeric / v_total_points) * 100)::int
+                   else 0 end;
+    
+    v_passed := v_score >= v_passing_score;
+
+    insert into quiz_attempt (attempt_number, completed_at, status, score, total_points, earned_points, passed, quiz_id,
+                              enrollment_id)
+    values (v_attempt_num, now(),
+            case when v_passed then 'PASSED' else 'FAILED' end,
+            v_score, v_total_points, v_earned_points, v_passed,
+            p_quiz_id, p_enrollment_id)
+    returning id into v_attempt_id;
+
+    insert into quiz_attempt_answer (correct, quiz_question_id, quiz_attempt_id)
+    select qao.correct,
+           (a ->> 'question_id')::bigint,
+           v_attempt_id
+    from jsonb_array_elements(p_answers) a
+             join quiz_answer_option qao on qao.id = (a ->> 'answer_option_id')::bigint;
+
+    insert into quiz_attempt_answer_selected_options (quiz_attempt_answer_id, quiz_answer_option_id)
+    select qaa.id, (a ->> 'answer_option_id')::bigint
+    from jsonb_array_elements(p_answers) a
+             join quiz_attempt_answer qaa
+                  on qaa.quiz_attempt_id = v_attempt_id
+                      and qaa.quiz_question_id = (a ->> 'question_id')::bigint;
+
+    return v_attempt_id;
+end;
+$$;
 }}}