CREATE OR REPLACE FUNCTION public.fn_prepare_notifications(p_event_type text, p_context jsonb, p_language text DEFAULT 'es'::text, p_course_id integer DEFAULT NULL::integer)
 RETURNS TABLE(to_email text, to_name text, subject text, body_html text, body_text text)
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
    r                    RECORD;
    v_course_rule_active boolean;
    v_is_mandatory       boolean;
BEGIN
    SELECT bool_or(mandatory) INTO v_is_mandatory
    FROM public.email_dispatch_rules
    WHERE event_type = p_event_type;

    IF p_course_id IS NOT NULL AND NOT COALESCE(v_is_mandatory, false) THEN
        SELECT cer.active INTO v_course_rule_active
        FROM public.course_email_rules cer
        WHERE cer.course_id = p_course_id AND cer.event_type = p_event_type;

        IF v_course_rule_active IS NOT NULL AND v_course_rule_active = false THEN
            RETURN;
        END IF;
    END IF;

    FOR r IN
        SELECT
            dr.email_field,
            dr.name_field,
            COALESCE(cet.subject,   et.subject)   AS raw_subject,
            COALESCE(cet.body_html, et.body_html) AS raw_html,
            COALESCE(cet.body_text, et.body_text) AS raw_text
        FROM   public.email_dispatch_rules dr
        JOIN   public.email_templates et
               ON  et.template_key = dr.template_key
               AND et.language     = COALESCE(p_language, dr.language)
        LEFT JOIN public.course_email_templates cet
               ON  p_course_id IS NOT NULL
               AND cet.course_id    = p_course_id
               AND cet.template_key = dr.template_key
               AND cet.language     = COALESCE(p_language, dr.language)
               AND cet.active       = true
        WHERE  dr.event_type = p_event_type
          AND  dr.active     = true
          AND  dr.language   = COALESCE(p_language, 'es')
          AND  et.active     = true
        ORDER BY dr.id
    LOOP
        to_email  := p_context ->> r.email_field;
        to_name   := p_context ->> r.name_field;
        subject   := public.fn_substitute_template(r.raw_subject, p_context);
        body_html := public.fn_substitute_template(r.raw_html,    p_context);
        body_text := public.fn_substitute_template(r.raw_text,    p_context);

        IF to_email IS NOT NULL AND to_email <> '' THEN
            RETURN NEXT;
        END IF;
    END LOOP;
END;
$function$
;