-- Helper function to get all customer IDs linked to a user
-- This function is used in RLS policies to check user access permissions
-- SECURITY DEFINER allows the function to run with the privileges of the creator
-- This is safe because the function only reads from customer_user table

CREATE OR REPLACE FUNCTION get_user_customer_ids(user_uuid UUID)
RETURNS TABLE(customer_id INTEGER) 
LANGUAGE plpgsql
SECURITY DEFINER
STABLE
AS $$
BEGIN
  RETURN QUERY
  SELECT cu.customer_id::INTEGER
  FROM customer_user cu
  WHERE cu.user_id = user_uuid;
END;
$$;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION get_user_customer_ids(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION get_user_customer_ids(UUID) TO anon;

-- Add comment for documentation
COMMENT ON FUNCTION get_user_customer_ids(UUID) IS 
'Returns all customer IDs that a user is linked to via the customer_user table. Used in RLS policies for data isolation.';

