-- Migration 026: fn_prepare_notifications with optional p_course_id
-- When p_course_id is provided:
--   1. If course_email_rules disables this event (and it is not mandatory), returns no rows.
--   2. For each dispatch rule, uses course_email_templates as override when available.

CREATE OR REPLACE FUNCTION public.fn_prepare_notifications(
  p_event_type text,
  p_context    jsonb,
  p_course_id  integer DEFAULT NULL
)
 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
    -- Check if the event type is mandatory (cannot be suppressed per course)
    SELECT bool_or(mandatory) INTO v_is_mandatory
    FROM public.email_dispatch_rules
    WHERE event_type = p_event_type;

    -- Check course-level override
    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 an override exists and is set to false, suppress all notifications for this event
        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     = 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     = dr.language
               AND cet.active       = true
        WHERE  dr.event_type = p_event_type
          AND  dr.active     = true
          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$;

GRANT EXECUTE ON FUNCTION public.fn_prepare_notifications(text, jsonb, integer) TO "elfi1953_stc-eu";