simulated_grades.sql

-- FUNCTION: public.simulate_grades()

-- DROP FUNCTION public.simulate_grades();

CREATE OR REPLACE FUNCTION public.simulate_grades(
	)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$

DECLARE
  -- Cursor to iterate over the schools in the "school_probs" table.
  school_cursor NO SCROLL CURSOR FOR SELECT DISTINCT "school","probs" FROM "school_probs" ORDER BY "school";
  -- Individual school record.
  current_school RECORD;
  -- Cursor to iterate over the individual student records in the "simulated_records" table.
  student_cursor NO SCROLL CURSOR (s text) FOR SELECT * FROM "simulated_records" 
    WHERE "school" = s ORDER BY "record_id";
  -- Individual student record.
  current_student RECORD;
  -- Number of students at a particular school.
  student_count INTEGER;
  -- Grade distribution for the school
  grade_dist INTEGER[];
  -- Counter for grade_dist assignment.
  i INTEGER;
  -- Random assignment number
  rand INTEGER;
  -- Calculated student count from rounding.
  calc_student_count INTEGER;
  -- Break for assignment loop.
  is_assigned BOOLEAN := FALSE;
BEGIN
  RAISE NOTICE 'Started executing simulate_grades() at %.', now();
  -- Open the school cursor.
  OPEN school_cursor;

  LOOP -- School entries.
    -- Fetch the next school record.
    FETCH school_cursor INTO current_school;
    EXIT WHEN NOT FOUND;
		
		RAISE INFO 'Assigning grades for school %', current_school.school;

    -- Open the student cursor based on the current school.
    OPEN student_cursor(current_school.school);
    -- Count the number of student records from a school.
    SELECT COUNT(*) INTO student_count FROM "simulated_records" WHERE "school" = current_school.school;
    
    RAISE DEBUG 'Current school: %, Number of students: %', current_school.school, student_count;

    -- Loop over the distribution array to calculate the correct distribution of grades from the number of students at the school.
    calc_student_count = 0;
    FOR i IN 1..6 LOOP
      grade_dist[i] = ROUND(current_school.probs[i] * student_count);
      calc_student_count = calc_student_count + grade_dist[i];
    END LOOP;

    IF calc_student_count != student_count THEN
      RAISE DEBUG 'Student counts did not match: % - %', student_count, calc_student_count;
      IF calc_student_count > student_count THEN
        grade_dist[6] = grade_dist[6] - (calc_student_count - student_count); -- Subtract students from the F assigned grade to match the correct number of students.
      ELSE
        grade_dist[6] = grade_dist[6] + (student_count - calc_student_count); -- Add students to the F assigned grade to match the correct number of students.
      END IF;
    END IF;

    RAISE DEBUG 'Grade distribution: %', grade_dist;

    LOOP -- Student entries
      -- Fetch the next student record.
      FETCH student_cursor INTO current_student;
      EXIT WHEN NOT FOUND;

      RAISE DEBUG 'Student record id: %', current_student.record_id;

      is_assigned = FALSE; -- Ensure is_assigned is false for each student before assignment.

      WHILE is_assigned = FALSE LOOP -- Assign a grade in a loop to ensure proper distribution of grades.

        rand := FLOOR(RANDOM() * 6 + 1)::int;

        -- Ensure rand is not zero.
        IF rand = 0 THEN
          RAISE EXCEPTION 'Random number was equal to zero';
        END IF;

        IF grade_dist[rand] > 0 THEN
          UPDATE "simulated_records" SET "grade" = (SELECT "grade" FROM "grade_values" WHERE "grade_values"."id" = rand) WHERE "simulated_records"."record_id" = current_student.record_id;
					grade_dist[rand] = grade_dist[rand] - 1;
          is_assigned = TRUE;
        END IF;

      END LOOP; -- is_assigned
    END LOOP; -- Student entries
    
    -- Loop clean-up.
    close student_cursor;
  END LOOP; -- School entries

  -- Final clean-up.
  CLOSE school_cursor;

  RAISE NOTICE 'Completed executing simulate_grades() at %', now();
END

$BODY$;