-- Migration 027: fn_tutor_list_students with optional p_course_id
-- NULL = all courses (returns course_id and course_name in every row).

CREATE OR REPLACE FUNCTION public.fn_tutor_list_students(
  p_tutor_id  bigint,
  p_course_id integer DEFAULT NULL
)
 RETURNS TABLE(
   student_id        bigint,
   student_type      text,
   student_name      text,
   student_surname   text,
   student_email     text,
   student_phone     text,
   student_country   text,
   level_id          integer,
   level_name        text,
   enrollment_status text,
   enrolled_at       timestamp with time zone,
   course_id         integer,
   course_name       text
 )
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN
    RETURN QUERY

    -- ── Registered partners ───────────────────────────────────────────────────
    SELECT
        sp.partner_id::BIGINT,
        'PARTNER'::TEXT,
        p.name::TEXT,
        p.surname::TEXT,
        p.email::TEXT,
        p.phone::TEXT,
        TRIM(p.country::TEXT),
        sp.level_id::INT,
        COALESCE(l.name::TEXT, 'Nivel ' || sp.level_id::TEXT),
        sp.status::TEXT,
        sp.updated_at::TIMESTAMPTZ,
        sp.course_id::INT,
        c.name::TEXT
    FROM   public.student_profiles sp
    JOIN   public.partners  p  ON p.partner_id = sp.partner_id
    LEFT JOIN public.levels l  ON l.level_id  = sp.level_id AND l.course_id = sp.course_id
    JOIN   public.courses   c  ON c.course_id = sp.course_id
    WHERE  sp.tutor_id  = p_tutor_id
      AND  (p_course_id IS NULL OR sp.course_id = p_course_id)

    UNION ALL

    -- ── Guest leads ───────────────────────────────────────────────────────────
    SELECT
        0::BIGINT,
        'GUEST'::TEXT,
        g.name::TEXT,
        g.surname::TEXT,
        g.email::TEXT,
        g.phone::TEXT,
        TRIM(g.country::TEXT),
        0::INT,
        COALESCE(l0.name::TEXT, 'Invitados'),
        'ACTIVE'::TEXT,
        g.reg_date::TIMESTAMPTZ,
        g.course_id::INT,
        c.name::TEXT
    FROM   public.guests g
    LEFT JOIN public.levels l0 ON l0.level_id = 0
    JOIN   public.courses   c  ON c.course_id = g.course_id
    WHERE  g.tutor_id   = p_tutor_id
      AND  (p_course_id IS NULL OR g.course_id = p_course_id)

    ORDER BY enrolled_at DESC NULLS LAST;
END;
$function$;

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