-- Migration 028: functions for per-course email rule and template management

-- ── Rules ────────────────────────────────────────────────────────────────────

-- Returns all global event types with their course-level override status
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,   -- NULL means no override
   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$;

-- Upsert a course-level rule override
CREATE OR REPLACE FUNCTION public.fn_admin_set_course_email_rule(
  p_course_id  integer,
  p_event_type text,
  p_active     boolean
)
 RETURNS void
 LANGUAGE sql
AS $function$
  INSERT INTO public.course_email_rules (course_id, event_type, active)
  VALUES (p_course_id, p_event_type, p_active)
  ON CONFLICT (course_id, event_type) DO UPDATE SET active = EXCLUDED.active;
$function$;

-- ── Templates ─────────────────────────────────────────────────────────────────

-- Returns course-specific template overrides (not global fallbacks)
CREATE OR REPLACE FUNCTION public.fn_admin_list_course_email_templates(p_course_id integer)
 RETURNS TABLE(
   template_key text,
   language     text,
   subject      text,
   body_html    text,
   body_text    text,
   active       boolean,
   updated_at   timestamptz
 )
 LANGUAGE sql
 STABLE
AS $function$
  SELECT
    template_key::text,
    language::text,
    subject,
    body_html,
    body_text,
    active,
    updated_at
  FROM public.course_email_templates
  WHERE course_id = p_course_id
  ORDER BY template_key, language;
$function$;

-- Upsert a course template override
CREATE OR REPLACE FUNCTION public.fn_admin_upsert_course_email_template(
  p_course_id    integer,
  p_template_key text,
  p_language     text,
  p_subject      text,
  p_body_html    text,
  p_body_text    text
)
 RETURNS void
 LANGUAGE sql
AS $function$
  INSERT INTO public.course_email_templates
    (course_id, template_key, language, subject, body_html, body_text, updated_at)
  VALUES
    (p_course_id, p_template_key, p_language, p_subject, p_body_html, p_body_text, now())
  ON CONFLICT (course_id, template_key, language) DO UPDATE SET
    subject    = EXCLUDED.subject,
    body_html  = EXCLUDED.body_html,
    body_text  = EXCLUDED.body_text,
    updated_at = now();
$function$;

-- Delete a course template override (reverts to global template)
CREATE OR REPLACE FUNCTION public.fn_admin_delete_course_email_template(
  p_course_id    integer,
  p_template_key text,
  p_language     text
)
 RETURNS void
 LANGUAGE sql
AS $function$
  DELETE FROM public.course_email_templates
  WHERE course_id   = p_course_id
    AND template_key = p_template_key
    AND language     = p_language;
$function$;

-- ── Grants ────────────────────────────────────────────────────────────────────

GRANT EXECUTE ON FUNCTION public.fn_admin_get_course_email_rules(integer)                            TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_set_course_email_rule(integer, text, boolean)              TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_list_course_email_templates(integer)                       TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_upsert_course_email_template(integer, text, text, text, text, text) TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_delete_course_email_template(integer, text, text)          TO "elfi1953_stc-eu";