{"-- Helper functions to avoid recursive RLS lookups
CREATE OR REPLACE FUNCTION public.current_user_is_org_admin_or_manager(org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles ur
    WHERE ur.user_id = auth.uid()
      AND ur.organization_id = org_id
      AND ur.role IN ('admin','manager')
  );
$$","CREATE OR REPLACE FUNCTION public.current_user_is_branch_manager_for_user(target_user_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.branch_roles br_mgr
    JOIN public.branch_roles br_user ON br_user.branch_id = br_mgr.branch_id
    WHERE br_mgr.user_id = auth.uid()
      AND br_mgr.role = 'branch_manager'
      AND br_user.user_id = target_user_id
  );
$$","-- Policies to allow managers to view user roles needed for filters
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies WHERE schemaname='public' AND tablename='user_roles' AND policyname='Org admins/managers can view org user roles'
  ) THEN
    CREATE POLICY \"Org admins/managers can view org user roles\"
    ON public.user_roles
    FOR SELECT
    USING (public.current_user_is_org_admin_or_manager(user_roles.organization_id));
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM pg_policies WHERE schemaname='public' AND tablename='user_roles' AND policyname='Branch managers can view branch user roles'
  ) THEN
    CREATE POLICY \"Branch managers can view branch user roles\"
    ON public.user_roles
    FOR SELECT
    USING (public.current_user_is_branch_manager_for_user(user_roles.user_id));
  END IF;
END $$"}