-- HU-ROOMS: Fix recursive RLS policies on public.users (42P17)
-- This script is idempotent and can be executed multiple times.

BEGIN;

ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

-- Use SECURITY DEFINER to avoid recursive RLS checks when resolving admin role.
CREATE OR REPLACE FUNCTION public.is_admin_user()
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  current_role TEXT;
BEGIN
  SELECT role
  INTO current_role
  FROM public.users
  WHERE id = auth.uid()
  LIMIT 1;

  RETURN current_role = 'admin';
END;
$$;

REVOKE ALL ON FUNCTION public.is_admin_user() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.is_admin_user() TO authenticated;
GRANT EXECUTE ON FUNCTION public.is_admin_user() TO service_role;

DO $$
DECLARE
  policy_record RECORD;
BEGIN
  FOR policy_record IN
    SELECT policyname
    FROM pg_policies
    WHERE schemaname = 'public'
      AND tablename = 'users'
  LOOP
    EXECUTE format('DROP POLICY IF EXISTS %I ON public.users', policy_record.policyname);
  END LOOP;
END;
$$;

CREATE POLICY users_select_self_or_admin
ON public.users
FOR SELECT
TO authenticated
USING (
  id = auth.uid()
  OR public.is_admin_user()
);

CREATE POLICY users_insert_self
ON public.users
FOR INSERT
TO authenticated
WITH CHECK (
  id = auth.uid()
);

CREATE POLICY users_update_self_or_admin
ON public.users
FOR UPDATE
TO authenticated
USING (
  id = auth.uid()
  OR public.is_admin_user()
)
WITH CHECK (
  id = auth.uid()
  OR public.is_admin_user()
);

CREATE POLICY users_service_role_all
ON public.users
FOR ALL
TO service_role
USING (true)
WITH CHECK (true);

COMMIT;
