CREATE OR REPLACE FUNCTION public.fn_admin_get_course_email_rules(p_course_id integer)
 RETURNS TABLE(event_type text, mandatory boolean, global_active boolean, course_active boolean, effective_active boolean)
 LANGUAGE sql
 STABLE
AS $function$
  SELECT
    dr.event_type::text,
    bool_or(dr.mandatory)                             AS mandatory,
    bool_or(dr.active)                                AS global_active,
    MAX(cer.active::int)::boolean                     AS course_active,
    CASE
      WHEN bool_or(dr.mandatory) THEN true
      WHEN MAX(cer.active::int) IS NOT NULL THEN MAX(cer.active::int)::boolean
      ELSE bool_or(dr.active)
    END                                               AS effective_active
  FROM public.email_dispatch_rules dr
  LEFT JOIN public.course_email_rules cer
         ON cer.event_type = dr.event_type
        AND cer.course_id  = p_course_id
  GROUP BY dr.event_type
  ORDER BY dr.event_type;
$function$
;