-- =============================================================================
-- shop_member_emails(_center_id)
--
-- SECURITY DEFINER lookup so staff (owner / trainer) of a center can
-- resolve auth.users.email for every membership in the same center
-- without exposing `auth.users` directly to the anon/authenticated
-- client roles.
--
-- Background: the member list previously joined `pending_invitations`
-- to surface email. Accounts that never went through the invite_member
-- Edge Function (dev seeds, future SSO imports, admin-created owners)
-- have no pending_invitations row → the MembersTab fell back to raw
-- UUIDs in the title. Audit integration test caught this.
--
-- Access gate: the function delegates to existing `is_shop_staff`
-- helper so only staff of the center can call it. Non-staff callers
-- receive an empty set.
-- =============================================================================

CREATE OR REPLACE FUNCTION public.shop_member_emails(_center_id uuid)
RETURNS TABLE(user_id uuid, email text)
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT cm.user_id, u.email::text
  FROM public.center_memberships cm
  JOIN auth.users u ON u.id = cm.user_id
  WHERE cm.center_id = _center_id
    AND public.is_shop_staff(auth.uid(), _center_id);
$$;

REVOKE ALL ON FUNCTION public.shop_member_emails(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.shop_member_emails(uuid) TO authenticated;
