-- 017 — Función fn_admin_list_guests para la pestaña Invitados en PartnersPage
-- Backup previo obligatorio antes de ejecutar.

CREATE OR REPLACE FUNCTION public.fn_admin_list_guests()
RETURNS TABLE(
    email         text,
    name          text,
    surname       text,
    country       text,
    tutor_id      bigint,
    tutor_name    text,
    tutor_surname text,
    reg_date      timestamp without time zone
)
LANGUAGE sql STABLE
AS $function$
    SELECT
        g.email::text,
        g.name::text,
        COALESCE(g.surname, '')::text   AS surname,
        COALESCE(g.country::text, '')   AS country,
        g.tutor_id,
        pt.name::text                   AS tutor_name,
        COALESCE(pt.surname, '')::text  AS tutor_surname,
        g.reg_date::timestamp without time zone
    FROM public.guests g
    LEFT JOIN public.partners pt ON g.tutor_id = pt.partner_id
    ORDER BY g.reg_date DESC
    LIMIT 500;
$function$;

-- Permisos
GRANT EXECUTE ON FUNCTION public.fn_admin_list_guests() TO "elfi1953_stc-eu";