-- Migration 025: fn_professor_list_courses — superadmin status from JWT, not DB role
--
-- Problem: the function queried vw_user_identity_roles.is_superadmin to decide
-- whether to show all courses. This bypassed the JWT elevation flow: a DB superadmin
-- who hadn't activated their role still saw all courses in the PROFESSOR tab.
--
-- Fix: add p_is_superadmin_active boolean parameter (DEFAULT false).
-- The backend passes req.user.allowed_roles.includes('SUPERADMIN') so the DB
-- function uses the JWT session state as the source of truth, not the DB role.
--
-- Drop old 1-param overload before applying (PG creates a new overload otherwise).

DROP FUNCTION IF EXISTS public.fn_professor_list_courses(bigint);

CREATE OR REPLACE FUNCTION public.fn_professor_list_courses(
  p_professor_id         bigint,
  p_is_superadmin_active boolean DEFAULT false
)
 RETURNS TABLE(course_id integer, name text, language text, active boolean, access_level text)
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN
  IF p_is_superadmin_active THEN
    RETURN QUERY
      SELECT c.course_id, c.name::text, c.language::text, c.active,
        CASE
          WHEN ca.professor_id IS NOT NULL THEN 'course_admin'
          ELSE 'superadmin'
        END::text AS access_level
      FROM public.courses c
      LEFT JOIN public.course_admins ca
             ON ca.professor_id = p_professor_id
            AND ca.course_id    = c.course_id
      ORDER BY c.name;
    RETURN;
  END IF;

  RETURN QUERY
    SELECT
      c.course_id,
      c.name::text,
      c.language::text,
      c.active,
      CASE
        WHEN ca.professor_id IS NOT NULL THEN 'course_admin'
        ELSE 'professor'
      END::text AS access_level
    FROM public.course_professor_levels cpl
    JOIN public.courses c ON c.course_id = cpl.course_id
    LEFT JOIN public.course_admins ca
           ON ca.professor_id = p_professor_id
          AND ca.course_id    = cpl.course_id
    WHERE cpl.professor_id = p_professor_id
    GROUP BY c.course_id, c.name, c.language, c.active, ca.professor_id
    ORDER BY c.name;
END;
$function$;

GRANT EXECUTE ON FUNCTION public.fn_professor_list_courses(bigint, boolean) TO "elfi1953_stc-eu";