Do not allow a student to open a completed sparkpoint in multiple sections to game pacing

prevent-double-credit-trigger.sql

SET search_path = 'mta-staging';

CREATE OR REPLACE FUNCTION ssas_guard_double_completion()
RETURNS trigger AS
$$
BEGIN
  -- Do not create a new active sparkpoint record if the sparkpoint has already been completed in another section
  PERFORM 1 FROM student_sparkpoint
           WHERE sparkpoint_id = NEW.sparkpoint_id
             AND student_id = NEW.student_id
             AND (
             	  assess_finish_time IS NOT NULL
                  OR (
                	 assess_override_time IS NOT NULL
                 AND assess_override_teacher_id IS NOT NULL
                     )
             );
  IF NOT FOUND THEN
  	return new;
  ELSE
    RAISE EXCEPTION 'This sparkpoint has already been completed in another section.';
  END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER ssas_guard_double_completion_trigger
	BEFORE INSERT ON section_student_active_sparkpoint
	FOR EACH ROW
	EXECUTE PROCEDURE ssas_guard_double_completion();