-- =============================================================================
-- center_memberships.display_name + shop_member_profiles fn
--
-- Renames the SECURITY DEFINER lookup from shop_member_emails to
-- shop_member_profiles and extends it to return display_name in
-- addition to email. Also adds the display_name column the function
-- reads from.
--
-- Why: rendering members by raw email (or worse, UUID) is a
-- product-grade UX failure. Trainers want human names. center_-
-- memberships now carries an optional per-shop display_name; the
-- profiles RPC returns both email + display_name in a single call so
-- the client can render `displayName ?? email ?? userId`.
--
-- A compatibility shim keeps the old shop_member_emails fn alive
-- pointing at shop_member_profiles, so any client that hasn't
-- redeployed continues to work.
-- =============================================================================

ALTER TABLE public.center_memberships
  ADD COLUMN IF NOT EXISTS display_name text;

DROP FUNCTION IF EXISTS public.shop_member_profiles(uuid);

CREATE OR REPLACE FUNCTION public.shop_member_profiles(_center_id uuid)
RETURNS TABLE(user_id uuid, email text, display_name text)
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT cm.user_id, u.email::text, cm.display_name
  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_profiles(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.shop_member_profiles(uuid) TO authenticated;

-- Compat shim: keep the old name resolving to the new fn so any
-- client that still calls shop_member_emails keeps working.
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 p.user_id, p.email FROM public.shop_member_profiles(_center_id) p;
$$;

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