CREATE OR REPLACE FUNCTION public.fn_student_get_enrollment(p_partner_id bigint, p_email text, p_course_id integer DEFAULT 1)
 RETURNS TABLE(student_type text, student_name text, student_surname text, student_email text, level_id integer, level_name text, enrollment_status text, course_id integer, course_name text, tutor_id bigint, tutor_name text, tutor_surname text, tutor_email text, enrolled_at text)
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN

  -- ── Path A: registered partner ─────────────────────────────────────────────
  IF p_partner_id > 0 THEN
    RETURN QUERY
    SELECT
      'PARTNER'::TEXT,
      e.student_name::TEXT,
      e.student_surname::TEXT,
      e.student_email::TEXT,
      e.level_id,
      e.level_name::TEXT,
      e.enrollment_status::TEXT,
      e.course_id::INT,
      COALESCE(c.name::TEXT, 'STC Core'),
      e.tutor_id,
      COALESCE(pt.name::TEXT,    ''),
      COALESCE(pt.surname::TEXT, ''),
      COALESCE(pt.email::TEXT,   ''),
      e.updated_at::TEXT
    FROM   public.vw_enrolled_student e
    LEFT JOIN public.courses  c  ON c.course_id   = e.course_id
    LEFT JOIN public.partners pt ON pt.partner_id = e.tutor_id
    WHERE  e.student_id = p_partner_id
      AND  e.course_id  = p_course_id;

  -- ── Path B: guest — look up by email ───────────────────────────────────────
  ELSE
    RETURN QUERY
    SELECT
      'GUEST'::TEXT,
      g.name::TEXT,
      g.surname::TEXT,
      g.guest_email::TEXT,
      0::INT,
      COALESCE(l0.name::TEXT, 'Introducción al STC'),
      'ACTIVE'::TEXT,
      g.course_id::INT,
      COALESCE(c.name::TEXT, 'STC Core'),
      g.tutor_id,
      COALESCE(g.tutor_name::TEXT,    ''),
      COALESCE(g.tutor_surname::TEXT, ''),
      COALESCE(g.tutor_email::TEXT,   ''),
      g.reg_date::TEXT
    FROM   public.vw_guest_with_tutor g
    LEFT JOIN public.levels l0 ON l0.level_id = 0
    LEFT JOIN public.courses c ON c.course_id  = g.course_id
    WHERE  LOWER(g.guest_email) = LOWER(p_email)
      AND  g.course_id          = p_course_id
    ORDER BY g.reg_date DESC
    LIMIT 1;
  END IF;

END;
$function$
;